Forum Discussion
Compare values from 2 rows
Need help!! How can I compare the data from two rows in a table?
I need to match “UnqNumber” field to find out missing "UnqNumber" in "SubmitTo" (2) of each Invoice.
One 'brute force' approach would be to seperate the base table into two in Power Query, each filtered respectively for the 1 or 2, then LEFT OUTER JOIN them together on Unique ID and look for nulls.
Another approach would be to create a measure that is the MAX(SubmitTo) and when you view the list by UniqueID, look for any where the MAX is 1 instead of 2.
I'm sure there are more ways.
Hi gauravnarchal ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
gauravnarchal , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.amitchandak - Each “UnqNumber” should exist twice (One as SubmitTo 1 & and other as SubmitTo 2) of each invoice no.
If it does not exist then it should return the Invoice as a "Missing".
So I wanted the measure to match/check "UnqNumber" of each Invoice with SubmitTo 1 & 2 and validate the missing.
I hope this makes some sense.
One 'brute force' approach would be to seperate the base table into two in Power Query, each filtered respectively for the 1 or 2, then LEFT OUTER JOIN them together on Unique ID and look for nulls.
Another approach would be to create a measure that is the MAX(SubmitTo) and when you view the list by UniqueID, look for any where the MAX is 1 instead of 2.
I'm sure there are more ways.
Hi gauravnarchal ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
