RELATED in Power Query

Hi Anonymous ,

If you're just needing to identify missing rows that should match with other tables, then you should do a merge in Power Query.

Select the table that contains the records that need to be matched, go to the Home tab > Merge Queries > Merge Queries as New.

In the dialog that opens, select the field from the first window that your tables are related on in the data model, then select your second table in the second window (the one that your RELATED function directs to), and select the corresponding field that this table is related on.

Start with using a LEFT OUTER join.

Once you hit ok, you will essentially have a single table that mimics the virtual table that is created when your tables have been related in the data model.

You should see blank rows on the right hand side of the join that don't match your left hand side table. Thse, presumably, will be the rows you need to reinstate.

You can experiment using RIGHT OUTER, FULL OUTER, LEFT/RIGHT ANTI joins in order to match up the tables in different ways, and manipulate which rows are displayed/removed during the join.

Pete