Forum Discussion
Conditionally merge in Power Query
Hi ImkeF & PBI Community,
I have a scenerio where I need to merge Fact table to two different Dimentions (Lookups) tables based on condition in Fact table, I know the standard method of doing it by merging both lookup table one by one with Fact table and then create a custom column but I want to avoid it instead considering Pro approach.
Condition: If FactTable[Destination] contains "EX" then DimCountry[Country] else DimDestination[Destination]
Thanks in advance
Avoid merges in Power Query wherever possible. Let the data model do the work for you.
Hi Anonymous ,
if you think merging tables is bad (for performance), the alternative (using a Table.SelectRows(YourDimTable, (dimTable)=> dimTable[keyColumn] = [keyColumnFactTable)) is even worse, as you are referencing the DimTable in each row of your fact table. Which would theroretically call it for each row unless you use a Table.Buffer before.
But with regards to the "Pro" approach: In standard star schemas is usually no place for fields from the dimension tables in fact tables. Fact tables should only contain the keyColumns to the dimension tables and values that are unique to the row in the fact table, like usually amounts and quantities.
If you need to reference values from a dimension table, do it in the data model like lbendlin suggested.
If the references in the data model don't work, because every other row should use a reference to a different dimension table, then considering pivoting some columns in your fact table so that the shapes match the star schema.
Or consider merging the dimension tables into one (depending on the use case). This dimension table would have a column like "Type" with entries either "Country" or "Destination". Then your condition from above ("If FactTable[Destination] contains "EX" ") would be used to create the new (Composite-)keyColumn to that new dim table.
4 Replies
Avoid merges in Power Query wherever possible. Let the data model do the work for you.
lbendlin
But the volume of data isn't too large!
Hi Anonymous ,
if you think merging tables is bad (for performance), the alternative (using a Table.SelectRows(YourDimTable, (dimTable)=> dimTable[keyColumn] = [keyColumnFactTable)) is even worse, as you are referencing the DimTable in each row of your fact table. Which would theroretically call it for each row unless you use a Table.Buffer before.
But with regards to the "Pro" approach: In standard star schemas is usually no place for fields from the dimension tables in fact tables. Fact tables should only contain the keyColumns to the dimension tables and values that are unique to the row in the fact table, like usually amounts and quantities.
If you need to reference values from a dimension table, do it in the data model like lbendlin suggested.
If the references in the data model don't work, because every other row should use a reference to a different dimension table, then considering pivoting some columns in your fact table so that the shapes match the star schema.
Or consider merging the dimension tables into one (depending on the use case). This dimension table would have a column like "Type" with entries either "Country" or "Destination". Then your condition from above ("If FactTable[Destination] contains "EX" ") would be used to create the new (Composite-)keyColumn to that new dim table.Thanks ImkeF for detailed explanation 🙂