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:

     

     

     

     

     

sevenhills's avatar
sevenhills
Super User
1 year ago

Power Query: Your attached file is using external files so I cannot provide the exact PQ. You can copy and paste the csv data in the first step of query in PQ and then reattach, either me or someone will help.

Power Query Approach

  1. Extract Unique Values

    • Reference Table_A, keep only the State column, and remove duplicates.

    • Reference Table_B, keep only the State column, and remove duplicates.

    • Append these two queries and remove duplicates again.

    • Adjust the data type if needed.

    • Name this table Dim_State.

  2. Repeat for Department

    • Follow the same steps for Department to create Dim_Department.

  3. Adjust the Data Model

    • Use Dim_State and Dim_Department as lookup tables.

    • Establish relationships with fact tables.

    • Use them for filtering.

  • sevenhills's avatar
    sevenhills
    Super User
    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:

     

     

     

     

     

    • sevenhills's avatar
      sevenhills
      Super User
      1 year ago

      Once all this is done and no errors, go to the "Report View" and use the Slicer values from Dim Department and Dim State.

       

      Note: if you want "null" as output, adjust the pq query above by removing the clause!

       

      Thanks

    • 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 !