RELATED in Power Query

[Anonymous]

Hello,

I usually use DAX, not Query so I struggle with a problem.

Because I have big source files, I deleted a lot of lines by using Query before working on Dax.

However, one of my user told me that there are a few exception lines that I deleted which souldn't be eliminated. These lines can be easily identified by using RELATED with another table and adding a simple formula. However I cannot do that in DAX because there lines are already deleted in Query.

My question is : Is it possible to do a related in Power Query ? If yes, what is the code to add in the request.

Thank you.

[Anonymous]

Thank you BA_Pete for your help. I will try to use this solution for my problem.

[BA_Pete · Accepted solution]

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