Loading a Table based based on another table

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:

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!

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.