Forum Discussion
Find missing records between two tables
Hello all,
I have two different data sources in two different databases / languages.
What I want to do is use Powerbi to automate & find which records are missing from database 2.
Database 1 will always contain all the records but database 2 will be missing records.
I have a attached a Pbix example here.
the data looks like this:
| database 1 - table: customers | database 2 - table: clientcustomers | |||
| Customer | CustomerID | Customer | CustomerID | |
| Walmart | 1 | Walmart | 1 | |
| Winco | 2 | Winco | 2 | |
| Target | 3 | Target | 3 | |
| Amazon | 4 | Amazon | 4 | |
| Costco | 5 | Costco | 5 | |
| Frenchs | 6 | |||
| Roses | 7 | |||
| Cash & Carry | 8 | |||
| Yokes | 9 | |||
and my desired output would show:
| Preferred Output (missing records from database 2) | |
| Customer | CustomerID |
| Frenchs | 6 |
| Roses | 7 |
| Cash & Carry | 8 |
| Yokes | 9 |
How can I accomplish this in Powerbi?
thank you!
Do an ANTI-JOIN in Power Query.
- Select the first table. You can create a reference to that table if you don't want to distrupt that table first.
- Select the Merge button on the Home Ribbon
- Configure the dialog box as below. My Database 1 Customers (2) is because I created a reference.
It returns this:
If you want to see the M code doing this, create a blank query and put this code in:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Donelet Source = #"Database 1 - Customers", #"Merged Queries" = Table.NestedJoin(Source, {"CustomerID"}, #"database 2 - clientcustomers", {"CustomerID"}, "database 2 - clientcustomers", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"database 2 - clientcustomers"}) in #"Removed Columns"
2 Replies
Do an ANTI-JOIN in Power Query.
- Select the first table. You can create a reference to that table if you don't want to distrupt that table first.
- Select the Merge button on the Home Ribbon
- Configure the dialog box as below. My Database 1 Customers (2) is because I created a reference.
It returns this:
If you want to see the M code doing this, create a blank query and put this code in:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Donelet Source = #"Database 1 - Customers", #"Merged Queries" = Table.NestedJoin(Source, {"CustomerID"}, #"database 2 - clientcustomers", {"CustomerID"}, "database 2 - clientcustomers", JoinKind.LeftAnti), #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"database 2 - clientcustomers"}) in #"Removed Columns"