Forum Discussion
comparing multiple values in two columns and displaying unique and matching values
Hey everyone,
I have two columns of ids that are from two different reports, I know for sure that there are more values in one column than the other but I want to know which are the Ids that aren't present in the other column and which ones are present.(I have thousands of these ids I want to compare, is there a query that can compare all the values one by one from both columns and match and provide an output)?
Data example:
Column1
| 905443 |
| 962910 |
| 963037 |
| 964534 |
| 964591 |
| 905463 |
| 964586 |
| 991158 |
| 964528 |
| 961843 |
| 961877 |
| 961869 |
Column2
| 994177 |
| 908419 |
| 908615 |
| 908395 |
| 908396 |
| 908402 |
| 908402 |
| 908402 |
Hi Anonymous
For the ones present in the first but not on the second column, create a new, one-column calculated table:
NewTable = EXCEPT ( DISTINCT ( Table1[Coulmn1] ), DISTINCT ( Table2[Coulmn2] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Anonymous
The unique values are from the first table in the EXCEPT( )
EXCEPT(Table1, Table2 ) gives you the values that are present in Table1 but not present in Table2. You can simply do
EXCEPT(Table2, Table1 ) if you want the values present in Table2 but not present in Table1
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
6 Replies
- AlB
Community Champion
Hi Anonymous
For the ones present in the first but not on the second column, create a new, one-column calculated table:
NewTable = EXCEPT ( DISTINCT ( Table1[Coulmn1] ), DISTINCT ( Table2[Coulmn2] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
- AlB
You've probably done something differently. Change the name of Column2 in table 2 to Column 1. Both columns in the expect should have the same name. From what I see there are no numbers in Table 2 present in Table1 in your example
See it all at work in the attached file.
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers