Loading a Table based based on another table

[ptmuldoon]

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

[JamesHowell · Accepted solution]

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:

Year	ItemNo.	Amount
2020	1	10
2019	1	20
2019	3	20

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 · Accepted solution]

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.

[Jimmy801]

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

[JamesHowell · Accepted solution]

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:

Year	ItemNo.	Amount
2020	1	10
2019	1	20
2019	3	20

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!