Forum Discussion

ptmuldoon's avatar
ptmuldoon
Resolver I
5 years ago
Solved

Loading a Table based based on another table

Is there a way to load a table into Excel only the rows of data that match in another table?

For example.  I have two tables in PowerQuery.  One called "Data" that contains accounting data for XX years.  That table includes a column called "itemNumber"

I have a separate table called "ItemsList" that list all the unique "itemNumber" and also has a column called "OnDashboard"  that has a Yes or No value.

So essentially, I want to pull into excel all the rows from the "Data" table that have Yes for the Dashboard.  I have a 1 to many relationship in the model already.   Just not sure how to possibly Load into a table just those specific rows.

Any help is appreciated.

Thanks,

PT

Need Help
  • JamesHowell's avatar
    JamesHowell
    5 years ago

    Hi ptmuldoon ,

     

    Using a Right Outer Join might be the approach you are looking for to load a table with just those specific rows.

     

    To achieve this I'd recommend setting up a staging table (connection only) of your ItemsList table in PowerQuery filtered to OnDashboard = Yes. Once you have that you can use the Merge Queries as New to merge the Data table and the stage_ItemsList Table using a RightOuter join. The right outer join will effectively filter your Data table to the records that are matched in the stage_ItemsList table.

    Here's s snip of how I set that up. 

     

    The result of which is:

     

    YearItemNo.Amount
    2020110
    2019120
    2019320

     

     

     

     

     

    If you need a more detailed explanation see Miguel Escobar's post on this. It is fantastic: Merge Operations in Power BI / Power Query – Part 2: Right Outer Join — Powered Solutions

     

    There are a few other ways to approach this, but this one might be the simplest. I hope this helps and good luck!

  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, ptmuldoon 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

     

    Table2:

     

    You may add a new step with the following m codes in 'Table1'.

    = let 
  l = Table.SelectRows(Table2,each [OnDashboard]="Yes")[ItemNumber],
  res = Table.SelectRows(#"Changed Type",each List.Contains(l,[ItemNumber]))
  in 
  res

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • JamesHowell's avatar
    JamesHowell
    Microsoft Employee
    5 years ago

    Hi ptmuldoon ,

     

    Using a Right Outer Join might be the approach you are looking for to load a table with just those specific rows.

     

    To achieve this I'd recommend setting up a staging table (connection only) of your ItemsList table in PowerQuery filtered to OnDashboard = Yes. Once you have that you can use the Merge Queries as New to merge the Data table and the stage_ItemsList Table using a RightOuter join. The right outer join will effectively filter your Data table to the records that are matched in the stage_ItemsList table.

    Here's s snip of how I set that up. 

     

    The result of which is:

     

    YearItemNo.Amount
    2020110
    2019120
    2019320

     

     

     

     

     

    If you need a more detailed explanation see Miguel Escobar's post on this. It is fantastic: Merge Operations in Power BI / Power Query – Part 2: Right Outer Join — Powered Solutions

     

    There are a few other ways to approach this, but this one might be the simplest. I hope this helps and good luck!

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello ptmuldoon 

     

    the solution from JamesHowell is great and for sure the way to go. I just want to add some minor detail. It's better to use the JoinKind.Inner, as the RightOuter would give you a null-value in your data-column whenever in your table OnDashboard would be an ID that in your data-table is not present. However, if this will never happen, RightOuter is also okay


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, ptmuldoon 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table1:

     

    Table2:

     

    You may add a new step with the following m codes in 'Table1'.

    = let 
  l = Table.SelectRows(Table2,each [OnDashboard]="Yes")[ItemNumber],
  res = Table.SelectRows(#"Changed Type",each List.Contains(l,[ItemNumber]))
  in 
  res

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.