Power BI: Combine multiple tables in long format

Question

I want to combine 3 tables using Power BI. This is what they currently look like.

I was able to combine them like this using this code:

Table.Combine({"Table1","Table2","Table3"}, {"Store","Name","Year"})

I would like to transform them in a long format like this

but combine only works for columns of the same name. I tried 'merge' but it changed the format to wide which I don't want. I tried 'transpose' but it made my table look like this which is not what I want:

store1 store1 store1
mary   john   roger
1968   2020   1984.... 

Does anyone have any advice? I checked online here, here and here but my case seems different.

Thank you in advance for any help or if you can point me to a similar question that was answered.

Here is my data in markdown format:

Store   Name    Year    Fav fruit   Fav veg
Store1  Mary    1968    apple       carrot
Store1  John    2020    pear        turnip
Store1  Roger   1984    plum        potato

Store   Name    Year    Height  Weight
Store2  Mary    1968    5ft     100kg
Store2  John    2020    6ft     90kg
Store2  Bob     1996    7ft     80kg

Store   Name    Year    Fav drink   Fav candy
Store3  Mary    1968    pepsi       kitkat
Store3  John    2020    coke        m&m
Store3  Henry   1978    fanta       hershey

Answer 1

Try

let Source = Table.UnpivotOtherColumns(Table1, {"Store", "Name", "Year"}, "Question", "Answer") & 
    Table.UnpivotOtherColumns(Table2, {"Store", "Name", "Year"}, "Question", "Answer") &
    Table.UnpivotOtherColumns(Table3, {"Store", "Name", "Year"}, "Question", "Answer")
in Source