cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fanofgolfdsm Regular Visitor
Regular Visitor

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

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Compare text columns in two different tables and document the differences

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
Sean Super Contributor
Super Contributor

Re: Compare text columns in two different tables and document the differences

@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

Vvelarde Super Contributor
Super Contributor

Re: Compare text columns in two different tables and document the differences

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

hemantkg Frequent Visitor
Frequent Visitor

Re: Compare text columns in two different tables and document the differences

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.

Re: Compare text columns in two different tables and document the differences

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!

ImkeF
MVP

Re: Compare text columns in two different tables and document the differences

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

 

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

 

 

😉

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




fanofgolfdsm Regular Visitor
Regular Visitor

Re: Compare text columns in two different tables and document the differences

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-

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,310)