Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
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
Hey thank you for your prompt response, I have already tried that but it shows "A table of multiple values was supplied where a single value was expected". Thoughts?
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
@AlB Thank you for the attached file, It worked, the only issue now is that I do not know if the unique values are from table 1 or table 2, hopefully that works when I sync the slicers from those tables.Thank you.
Kind Regards,
Shlok Shah
@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
The above post column 1 & column 2 were just sample data, I have column values mathcing other column values. I tried changing the names to same names but it did not work either.
Can one of the reason be that because lets say a value is in row 1 in column 1 and the value matching that value in column 2 is in row 223, could that be the reason? or does the formula check iteratively for all the values if they are matching or not?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |