How can I find rows from an Order table that do not have matching LineItem rows using Power BI?

Question

I have two tables and one of them has orphan records. I'm just starting to dive into Power BI. I know how to solve this problem using SQL Server and I assume it's not that hard to solve in Power BI. However, Power BI has me stumped at the moment. Using Power BI, how can I find rows from an Order table that do not have matching LineItem rows?

This is what the solution would be in SQL. I'm trying to solve this without leaving Power BI.

SELECT DISTINCT 
    [Order].[OrderId]
FROM 
    [dbo].[Order]
    LEFT JOIN [dbo].[LineItem] ON [Order].OrderId = [LineItem].[OrderId]
WHERE 
    [LineItem].[OrderId] IS NULL

Is that LineItem a column on another table? Could you provide some sample data? And maybe desired output? Power BI is very flexible, with M and DAX, so there are multiple ways to do it. — mxix
LineItem is another table. The desired output is a list of OrderIDs without matching LineItem rows. — Tarzan

mxix mxix · Accepted Answer · 2019-03-27T22:30:48

To resolve it with M, in your Power Query Editor.

Load both tables, and apply a Merge operation.

Choose both tables and key columns and pick

Left Anti (only rows on the first table) option for Join Kind

That should outcome rows that have no match on the second table.

How can I find rows from an Order table that do not have matching LineItem rows using Power BI?

1 Answers