cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Shlok_M_Shah Regular Visitor
Regular Visitor

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
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: comparing multiple values in two columns and displaying unique and matching values

Hi @Shlok_M_Shah 

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  Datanaut

View solution in original post

Super User III
Super User III

Re: comparing multiple values in two columns and displaying unique and matching values

@Shlok_M_Shah 

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  Datanaut

 

View solution in original post

6 REPLIES 6
Super User III
Super User III

Re: comparing multiple values in two columns and displaying unique and matching values

Hi @Shlok_M_Shah 

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  Datanaut

View solution in original post

Shlok_M_Shah Regular Visitor
Regular Visitor

Re: comparing multiple values in two columns and displaying unique and matching values

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?

Super User III
Super User III

Re: comparing multiple values in two columns and displaying unique and matching values

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  Datanaut

 

Shlok_M_Shah Regular Visitor
Regular Visitor

Re: comparing multiple values in two columns and displaying unique and matching values

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?

Shlok_M_Shah Regular Visitor
Regular Visitor

Re: comparing multiple values in two columns and displaying unique and matching values

@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

Super User III
Super User III

Re: comparing multiple values in two columns and displaying unique and matching values

@Shlok_M_Shah 

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  Datanaut

 

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors