Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ShawnTRizzle
Regular Visitor

Comparing 2 tables that have 2 columns each and return difference

I need to compare 2 tables with 2 columns in each.  I need for it to pull back the ones in red below. I am dealing with thousands of numbers so I need it to pull them out for me. I can't scroll down and look for them. 

 

Example: 

 

TABLE 1

    A              B

A1B1
A2B2
A3B3
A4B4
A5B5
A6B6
A7B7
A8B8
A9B9
A10B10
A11B11
A12B12
A13B13
A14B14
A15B15
A16B16
A17B17
A18B18
A19B19
A20B20
A21B21
A22B22
A23B23
A24B24
A25B25
A26B26

 

 TABLE 2

   A             B

A1B1
A2B2
A3B3
A4S7
A5B5
A6B6
A7B7
A8B8
A9BX
A10B10
A11B11
A12B12
A13B13
A14B14
A15B15
A16B16
A17B17
A18B18
A19B19
A20CX
A21B21
A22B22
A23B23
A24B24
A25B25
A26R78
2 ACCEPTED SOLUTIONS

Hi

Add in each table anID column with the concatenation of the two columns:
IDTABLE1 = Table1[A] & Table1[B]

IDTABLE2 = Table2[A] & Table2[B]

Then use the same formula as I said but replace the A column by the ID columns.

Valid = IF(
Table1[IDTABLE1] = LOOKUPVALUE (Table2[IDTABLE2], Table2[IDTABLE1], Table1[IDTABLE1]),
"OK",
" NOT OK"
)
Regards
Mfelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi @ShawnTRizzle,

 

You can add to your table a filter and select the OK / NOT OK, or you can try to do 2 new tables with the following formula: 

OK_Table = CALCULATETABLE(Table1,Table1[Valid]="OK")


NOT_OK_Table = CALCULATETABLE(Table1,Table1[Valid]="NOT OK")

Regards,

MFelx


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

13 REPLIES 13
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @ShawnTRizzle,

 

Create a new table to list all those unmatched rows using below formula:

New Table = EXCEPT(TABLE2,TABLE1)

1.PNG

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Quick question, how do you do the reverse and pull back the OK ones? 

Hi @ShawnTRizzle,

Not sure.whst you mean do the reverse I gave you two.formulas one gives yous the ok other the not ok you will get two tables.

Regards
MFelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Sorry, that question was for someone else. Your formulas worked perfectly. Thanks again!

Maybe I wasn't clear but just in case, Column A & Column B go together on each table. So A & B must match A & B. 

MFelix
Super User
Super User

Hi @ShawnTRizzle,

I'm assuming the in column A the values are the same in both tables and have the same number of rows add the following column in table 1:
Valid = IF(
Table1[B] = LOOKUPVALUE (Table2[B], Table2[A], Table1[A]),
"OK",
" NOT OK")

Then just add the Table1[A] to a table visual along with the Valid column and filter by NOT OK.

Not on the computer but believe the sintax is correct.

Regards

MFelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Well, that is the thing. Either Column may have different data. They are SUPPOSED to be identical but errors were made on both sides. I need all differences to be pointed out so I can investigate. We had a vendor move thousands of boxes with our barcodes on them and they added their barcodes. They were supposed to record our old one and their new one. They miscanned some of the boxes, hit the wrong barcodes, that type of thing. I need to compare their worksheet to our worksheet that we created before they took them. Our scanners also had issues so we can't rely on our data either. Saving grace is a majority of them are correct and do match. I just need the small anomalies picked out. 

 

 

Hi

Add in each table anID column with the concatenation of the two columns:
IDTABLE1 = Table1[A] & Table1[B]

IDTABLE2 = Table2[A] & Table2[B]

Then use the same formula as I said but replace the A column by the ID columns.

Valid = IF(
Table1[IDTABLE1] = LOOKUPVALUE (Table2[IDTABLE2], Table2[IDTABLE1], Table1[IDTABLE1]),
"OK",
" NOT OK"
)
Regards
Mfelix

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



So is there a way to create a 3rd and 4th table that will pull all rows that say OK for one table and NOT OK for the other? The OK ones need to be sent to IT and the NOT OK ones need to be investigated by myself. 

Hi

Add in each table anID column with the concatenation of the two columns:
IDTABLE1 = Table1[A] & Table1[B]

IDTABLE2 = Table2[A] & Table2[B]

Then use the same formula as I said but replace the A column by the ID columns.

Valid = IF(
Table1[IDTABLE1] = LOOKUPVALUE (Table2[IDTABLE2], Table2[IDTABLE1], Table1[IDTABLE1]),
"OK",
" NOT OK"
)
Regards
Mfelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



I did that and it works. I will be dealing with 1000's of numbers. I need for it to pull a list of lines that are "OK" and "NOT OK". Two seperate tables. 

Hi @ShawnTRizzle,

 

You can add to your table a filter and select the OK / NOT OK, or you can try to do 2 new tables with the following formula: 

OK_Table = CALCULATETABLE(Table1,Table1[Valid]="OK")


NOT_OK_Table = CALCULATETABLE(Table1,Table1[Valid]="NOT OK")

Regards,

MFelx


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



You are awesome. Thanks for your help!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.