Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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"}))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
61 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |