Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
A1 | B1 |
A2 | B2 |
A3 | B3 |
A4 | B4 |
A5 | B5 |
A6 | B6 |
A7 | B7 |
A8 | B8 |
A9 | B9 |
A10 | B10 |
A11 | B11 |
A12 | B12 |
A13 | B13 |
A14 | B14 |
A15 | B15 |
A16 | B16 |
A17 | B17 |
A18 | B18 |
A19 | B19 |
A20 | B20 |
A21 | B21 |
A22 | B22 |
A23 | B23 |
A24 | B24 |
A25 | B25 |
A26 | B26 |
TABLE 2
A B
A1 | B1 |
A2 | B2 |
A3 | B3 |
A4 | S7 |
A5 | B5 |
A6 | B6 |
A7 | B7 |
A8 | B8 |
A9 | BX |
A10 | B10 |
A11 | B11 |
A12 | B12 |
A13 | B13 |
A14 | B14 |
A15 | B15 |
A16 | B16 |
A17 | B17 |
A18 | B18 |
A19 | B19 |
A20 | CX |
A21 | B21 |
A22 | B22 |
A23 | B23 |
A24 | B24 |
A25 | B25 |
A26 | R78 |
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @ShawnTRizzle,
Create a new table to list all those unmatched rows using below formula:
New Table = EXCEPT(TABLE2,TABLE1)
Regards,
Yuliana Gu
Quick question, how do you do the reverse and pull back the OK ones?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSorry, 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWell, 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsSo 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou are awesome. Thanks for your help!
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |