Forum Discussion

ERing's avatar
ERing
Post Partisan
1 year ago
Solved

Is this simple Data Model correct?

I have a report currently set up with the data model below. I have two tables (Table_A, Table_B) and I need my report to show a table visual with measures from both tables.   I usually try to have ...
Need Help
  • sevenhills's avatar
    sevenhills
    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:

     

     

     

     

     

ERing's avatar
ERing
Post Partisan
1 year ago

sevenhills Thanks very much! I was able to replicate this in my file by using the Advanced Editor. 

I just thought of another use case and wondering if you can help here.

I will likely have a situation with my real data where my tables will:
1. Have columns named differently (but they are the same dimension). For example, in Table_A "State" will be called "State", but in Table_B "State" will be called "Location".


2. The "State" column in Table_A may have a different set of states compared to the "Location" column in Table_B. For example, Table_A.State may contain records of (Florida, Georgia, and South Carolina) while Table_B.Location may contain records of (New York, California, Texas).

How would I adjust the Power Query code to account for the dimension columns being named differently in each table?

I assume I would change Source1 to the following?
Source1 = Table_B,
#Removed Other Columns1" = Table.SelectColumns(Source1,{"Location"})

My confusion is how to address this part of the code when the column in Table_B is named "Location" rather than State.
#Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [State]  <> null and [State] <> "" and [State] <> "null")

SAMPLE FILE 

Thanks again for all your help!



sevenhills's avatar
sevenhills
Super User
1 year ago

This concept is called role playing dimensions. Like date can be sales date, order date, purchase date. 

 

Coming back to your model, PQ: You are in the right track: We will rename and bring it as common column name, this is the simple technique 🙂 

 

See if this helps!

 

let 
    Source = Table_A,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"State"}), 

    Source1 = Table_B,
    #"Removed Other Columns1" = Table.SelectColumns(Source1,{"Location"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Location", "State"}}), 

    SourceCombined = Table.Combine({#"Removed Other Columns", #"Renamed Columns"}),
    #"Removed Duplicates" = Table.Distinct(SourceCombined),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [State] <> null and [State] <> "" and [State] <> "null")
in
    #"Filtered Rows"

 

and in the data model, link this table column to both state and location in respective tables. 

 

Let us know how it goes !