Find & List Unique Values Between Two Columns

Question

I am needing to identify and then list unique values that have been captured in Column A but not in Column B on a separate column. This appears to be a simple process but for some reason i have not found the solution.

Below is a simple illustration, the data set I am working with is much larger +100,000 rows

Column A	Column B	Solution
2113	9986	001B
1254	7845	2856
001B	2113	4568
2856	1254
4568	001A

anonymous · Answer

You can create a new table with:Table = 
EXCEPT( values( Table1[Column1]), values( Table1[Column2] ))Depending on your goals, you could also just use that a table function in a measure, so that it's only called when used.&nbsp;Also could do this in powery query&nbsp;&nbsp;

rhcentennialh · Answer

I should have mentioned this previously, I generated/calculated Column A & B from a parent column (Column C).

Is there a way to show the unique values between A & B without creating tables seeing as Column A & B are calculated columns?

anonymous · Answer

I dont see why not. Any chance you upload some sample data?

rhcentennialh · Answer

See below for a sample of the data and my steps as they currently are.&nbsp;Step 1. Create two calculated&nbsp; &nbsp;Calculated Column 1 - List values that appear in 2018&nbsp; &nbsp;Calculated Column 2 - List values that appear in 2019&nbsp;Step 2 (The Issue). How to reference the two calculated column lists to show what values have appeared in 2018 but not in 2019 yet.&nbsp;&nbsp;Data Example:ValueYearB96892018H400192018H400192018H400192019J01902018M35002018M35002018M35002019M41272018M41272019M51362018M85802018N2022018N3902018Z00002018Z232018Z68232018Z68242019B3492018A0842018M255112019M255112019M255612019Z12112018W19XXXA2019H400032019E7822018E7822019M75812019Z874422018Z874422019

anonymous · Answer

You can build a table using:Table = 
 EXCEPT(
   SELECTCOLUMNS(
     CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year]))),
     "Values", CALCULATE(VALUES( Table1[Value] ))
     ),

SELECTCOLUMNS(
    CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year])-1)),
    "Values", CALCULATE(VALUES( Table1[Value] ))
)
 )depending on your needs, you could use that same logic in a measure. the following with concatenate a list of records based on the year that is on the rows. So becomes a little more dynamic:Values Not Appearing in Current Year = 
CONCATENATEX(
 EXCEPT(
   SELECTCOLUMNS(
     CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year])-1)),
     "Values", CALCULATE(VALUES( Table1[Value] ))
     ),

SELECTCOLUMNS(
    CALCULATETABLE( Table1, FILTER( ALL( Table1), Table1[Year] = MAX( Table1[Year]))),
    "Values", CALCULATE(VALUES( Table1[Value] ))
)
 ),
[Values],UNICHAR(10) )

Forum Discussion

Find & List Unique Values Between Two Columns

10 Replies

Related content

Unique number column

Unique Count in Row convert

unique value in measure column

Will Unique include the null's count in column profiling in Power Query Editor?

Unique Values Across Two Columns Using SUMMARIZE