Compare text columns in two different tables and document the differences

[fanofgolfdsm]

I have two separate tables that I'm trying to compare and determine what the differences are. Table A has a list of all the plants currently available. Table B has a list of the plants currently in service. I'd like to know the plants that are currently not in service. An example is listed below.

Table A	Table B	Expected Result
Plant 1	Plant 3	Plant 1
Plant 2	Plant 6	Plant 2
Plant 3	Plant 7	Plant 4
Plant 4		Plant 5
Plant 5
Plant 6
Plant 7

Is it possible to create a dynamic list of the plants not in service?

[Sean]

@fanofgolfdsm

1) Go to the Query Editor - select Table A (in the Queries on the left side) - Home tab - click Merge Queries - Merge Queries As New

2) select the plant column in Table A - select Table B from the second drop-down - select Plant Column in Table B

3) Join Kind - select Left Anti(rows only in first) - OK

4) select new column - right-click - Remove

Hope this helps!

[Vvelarde · Accepted solution]

@fanofgolfdsm

A: If you want just a visual to show this:

Assuming that are related

Create a Measure: InTableB = COUNTROWS(Table2)

Use a Visual

Values - Plant of Table A

Visual Filter : InTableB is Blank

B: Want a New Table:

Create a new table in DAX.  (Modeling--New Table)

Assuming that are only 1 column on each table:

NewTable = EXCEPT('Table1';Table2)

And if you have 2 or more columns:

NewTable = EXCEPT(VALUES(Table1[Plant]),VALUES(Table2[Plant]))

[hemantkg]

Another simpler way to do is:

Goto Relationship and join table Table A and B, hope this is already the case.

use the Table A to put into any visualization and Table B as Visual level fitler (if not required for other visuals on the report/page)

Select Blank as the filter value and you are done.

[Phil_Seamark]

In DAX the formula would be this

New Table = EXCEPT('Table A','Table B')

[ImkeF]

In the query-editor, the M-formula would look like this:

Difference = List.Difference( { TableA[ColumnName], TableB[ColumnName] } )

😉

[fanofgolfdsm]

Thanks to everyone that provided possibilities to solve my problem.

Vvelarde, your solution for the measure worked beautifully. My actual tables were more complicated than I posted but I could use COUNTROWS and the IsBlank filter with the other slicers I had on my report and get exactly what I needed. Thanks.

-Scott-