Find missing records between two tables

[Anonymous]

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!

[edhans · Accepted solution]

Do an ANTI-JOIN in Power Query.

1. Select the first table. You can create a reference to that table if you don't want to distrupt that table first.
2. Select the Merge button on the Home Ribbon
3. 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 Done

let
    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"

[Anonymous]

thank you for the quick response!!!! I will try later tonight and follow up if I have anymore questions.