Union Distinct multiple columns in Power Query M

[JemmaD]

Hi there, 

I have four fact tables and I need to create a dimension table pulling two columns from each of the four fact tables.

Let's say the fact tables are named [Table1] [Table2] [Table3] [Table4]

And the two columns I need from each are called [Product] [Branch]

I am able to union distinct on one column, but I can't get the syntax right for two columns.

Here is my code for the one column, can you help me add in the second?

= Table.Distinct(Table.Combine({
Table.FromColumns ({Table1[Product]}, {"Product"}),
Table.FromColumns ({Table2[Product]}, {"Product"}),
Table.FromColumns ({Table3[PRODUCT]}, {"Product"}),
Table.FromColumns ({Table4[PRODUCT]}, {"Product"})
}))

[Ahmedx]

pls try this

Table.Distinct(
   Table1[[Product],[Branch] ] & 
   Table2[[Product] ,[Branch] ]&
   Table3[[Product] ,[Branch] ]&
   Table4[[Product] ,[Branch] ],
{"Product", "Branch"})

[spinfuzer · Accepted solution]

The problem here is that you have differing column names.  You could make this problem a lot easier by making the column names the same.  A simple Table.Combine would work if all the names were the same.  Because they are not, you can use something like the below.

= Table.Distinct(Table.FromColumns(
        List.Transform(
            List.Zip({
                Table.ToColumns(Table1[[Product],[Branch]]), 
                Table.ToColumns(Table2[[Product],[Branch]]), 
                Table.ToColumns(Table3[[PRODUCT],[Branch]]),
                Table.ToColumns(Table4[[Product],[BRANCH]])
            }), 
        List.Combine), 
{"Product", "Branch"}))