List differences between two columns of two table

[Frenchtom811]

Hello everyone, I have a good one for you!  Please refer to the below screen grab.

I have two tables.  The first table contains item numbers listed on each customer's price sheet.  The item numbers are class specific meaning that a particular item number will not appear in more than one class.  However, a particular item number can and often does appear under more than one customer.  Table 2 is the master item number file.   Every item number is unique (non-repeating) and is assigned to a single class.

I am trying to come up with a way of listing the differences between what the customer has in their price sheet (Customer Table) and what is in the Master Table.  This will tell me the customers who are missing item numbers as well as the identity of these missing item numbers.  Referring to the below screen grab, if I set my slicer to Class A, Joe and Bob would be listed with their respective missing item numbers.  If I selected Class B, I would get no results becuase only Billy has access to Class B and his price sheet contains all available Class B item numbers.  If I selected Class C, the output woould show Joe = 330, 331 and 333; Bob = 330 and 331.

Any idea how I can do this?  Most content I have found calls for duplicating tables and making a list comparison.  I was hoping there is an easier way.  Thanks!  

[Anonymous · Accepted solution]

Hi  @Frenchtom811  ,

Here are the steps you can follow：

1. Enter Power query, select Merge queries as New in Home, use [Customer Table] as the main table, column Class as the associated point, and select Left Outer

2. Select all the columns of the merged table in the power query, right-click and select Remove Duplicates

3. Create calculated column.

Flag =
var _1=
SELECTCOLUMNS(FILTER(ALLSELECTED('Customer Table'),[Class]=EARLIER([Class])),"1",[Item Number])
return
IF([Item Number] in _1,1,0)

4. Place [Flag] in Filters, is=0

5. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Frenchtom811]

Hello @Anonymous , you did it! I will mark yours as the solution however there is one minor correction.  The Master Table is the main table and the customer table is secondary.

[parry2k]

@Frenchtom811 can you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.