cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fanofgolfdsm
Helper I
Helper I

Compare text columns in two different tables and document the differences

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 ATable BExpected Result
Plant 1Plant 3Plant 1
Plant 2Plant 6Plant 2
Plant 3Plant 7Plant 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?

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@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]))

 

 

 

 

 

 

 




Lima - Peru

View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

In DAX the formula would be this

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

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

 

 

😉

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Vvelarde
Community Champion
Community Champion

@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]))

 

 

 

 

 

 

 




Lima - Peru

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-

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.

Sean
Community Champion
Community Champion

@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! Smiley Happy

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors