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

Hi gmsamborn Thansk for you solutions. I see how the DAX example works

I'd also like to see how the PQ version works, but it looks like there is some issue with the file path.


Do you know what the error here is?




  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    Hi ERing 

     

    Sorry about the path names. To be able to do this in Power Query, I first had to export the existing data and then import it into a new copy.

    I made the assumption that all departments and all states would be in 'Table_A'.  If that isn't the case, it's not hard to change.


    In Power Query:
    - Make a reference to your 'Table_A'
    - select the [Department] column
    - remove duplicates
    - rename to DimDepartment


    - Make a reference to your 'Table_A'
    - select the [State] column
    - remove duplicates
    - rename to DimState

     

    Exit Power Query

     

    I hope this makes sense.

     

    • ERing's avatar
      ERing
      Post Partisan
      1 year ago

      The data I provided used Excel as a data source. The data in my real report uses Snowflake.

      I'm unsure how I can create a DIM_Department table and a DIM_State table in Power Query using the tables I have loaded into Power BI from Snowflake.