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.