Comparing two sets of data

[Anonymous]

Hi,

I am trying to run a query where the I can see all the orders from table 1 that exist in table 2 where the order status does not match. Please see example of tables

Table 1

Order No	Order Status
1234	Resolved
1235	Pending
1236	Open
1237	Resolved

Table 2

Order No	Order Status
1234	Open
1235	Resolved
1236	Pending
1237	Resolved

@PowerBI please can someone help? I have tried doing a merge ant left query but this gives me only the records in the first table that do not exisit in the second table.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Select the Order No column of the two tables to merge.

Then select the required field and expand it.

Finally add a conditional column

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks - also do I need to use order no and order status as both the keys to join? 

[CNENFRNL]

No, use only order no as key to join the two tables.

[Anonymous]

Thanks. how do I see the different order status values from both tables? Do I need to expand th query to show status prefixed ?

And I need to count of all the mismatch records so would this be the correct way Total Mismatch Orders:=COUNTROWS(table1)

[CNENFRNL]

Table1 ( left outer join ) Table2

instead of anti-join.