Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing two sets of data

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 NoOrder Status
1234Resolved
1235Pending
1236Open
1237Resolved

 

Table 2

Order NoOrder Status
1234Open
1235Resolved
1236

Pending

1237Resolved

 

 

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.

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Table1 ( left outer join ) Table2

    instead of anti-join.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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)