Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"})
}))
Solved! Go to 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"}))
pls try this
Table.Distinct(
Table1[[Product],[Branch] ] &
Table2[[Product] ,[Branch] ]&
Table3[[Product] ,[Branch] ]&
Table4[[Product] ,[Branch] ],
{"Product", "Branch"})
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"}))