Forum Discussion
Is this simple Data Model correct?
- 1 year ago
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:
gmsamborn sevenhills v-dineshya danextian
I'm still struggling with a solution for this. I used DAX to create tables for DIM_STATE and DIM_DEPARTMENT.
The real data in my real report has blank values for State and blank values for Department. Thus the DIM tables I'm creating also result in a Blank row.
Is there any way I can solve this? Open to doing this in Power Query as well if anyone can provide guidance on how to do so.
DIM_STATE = DISTINCT(UNION(DISTINCT('Table_A[State]),DISTINCT('Table_B'[State])))
DIM_DEPARTMENT = DISTINCT(UNION(DISTINCT('Table_A[Department),DISTINCT('Table_B'[Department])))