Forum Discussion
Is this simple Data Model correct?
In Power Query, Create query "DIM_STATE" as below:
let Source = Table_A, #"Removed Other Columns" = Table.SelectColumns(Source,{"State"}), Source1 = Table_B, #"Removed Other Columns1" = Table.SelectColumns(Source1,{"State"}), SourceCombined = Table.Combine({#"Removed Other Columns", #"Removed Other Columns1"}), #"Removed Duplicates" = Table.Distinct(SourceCombined), #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [State] <> null and [State] <> "" and [State] <> "null") in #"Filtered Rows"Output:
----------------------------------------------------------
Similarly for "DIM_DEPARTMENT"
let Source = Table_A, #"Removed Other Columns" = Table.SelectColumns(Source,{"Department"}), Source1 = Table_B, #"Removed Other Columns1" = Table.SelectColumns(Source1,{"Department"}), SourceCombined = Table.Combine({#"Removed Other Columns", #"Removed Other Columns1"}), #"Removed Duplicates" = Table.Distinct(SourceCombined), #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Department] <> null and [Department] <> "" and [Department] <> "null") in #"Filtered Rows"-----------------------------------------------------------
Close and apply changes
--------------------------------------------------------
Adjust the data model like below:
Right now, your measures may be correct. But the many-to-many relationship between Table_A and Table_B on Department can cause filter confusion, wrong results, or performance issues later as the model grows. To future-proof it, it's better to create a separate Department table and connect both Table_A and Table_B to it with one-to-many relationships.
You can create this calculated table:
Departments = DISTINCT ( UNION ( SELECTCOLUMNS ( Table_A, Table_A[Department] ), SELECTCOLUMNS ( Table_B, Table_B[Department] ) ) )In Power Query, you would need to append Tables A and B as a new query, keep only the Department column, and then remove duplicates to create the list.
The decision between DAX and Power Query depends on the situation. If the transformation required to extract the departments is very complex or if it involves working with a large dataset before reaching the final list, then creating the dimension table using DAX would be the better approach for efficiency. It avoids recalculating or reloading data in Power Query.
Note:
When appending queries as a new query in Power Query, the referenced queries (Tables A and B) are still fully reevaluated during refresh. It's as if the entire logic of both tables is brought inside the new appended query. This means any heavy transformations done earlier on Tables A and B will still impact the refresh time of the appended result. It doesn't isolate or "freeze" the earlier steps. Any changes made to the referenced queries will also cause the append version to re-evaluate.
For straightforward or lightweight transformations, doing it in Power Query is typically preferable.danextian Thanks for the detail.
I thought Append Queries only works if the two tables have the exact same columns? In this example my tables have different columns.
Would appending queries still be an option?
The data in my real tables has records with Blank Department and records with Blank State. How can I remove these from the calculated table?Table_A
- Date
- Technician_Name
- State
- Department
- Regular_Hours
- Overtime_Hours
- Training_Hours
- Miscellaneous_Hours
- Sales_Revenue
- Add_On_Revenue
Table_B
- Date
- State
- Department
- Jobs_Ran
- Total_Survey_Responses
- Positive_Survey_Responses
- Negative_Survey_Responses
- Customer_Complaints