Find duplicate in one column and then return column that don't match

[marcelmunk]

Hi,

Please, I need some help.

I have items with a series of duplicated columns, and one that don't match. I want Power BI to tell me which column is not a duplicate. (See example data below, in which I used only 4 columns to simplify)

Input:

Product	Color	Size	Slim
ABC	Blue	S	Y
ABC	Blue	S	N
DEF	Red	M	Y
XYZ	Blue	L	Y
XYZ	Blue	M	Y
KLM	Black	M	Y
KLM	White	M	Y

Desired result:

Product	DiffColumn
ABC	Slim
XYZ	Size
KLM	Color

Thanks in advamce,

Marcel

[Anonymous]

Hi @marcelmunk ,

There seems to be no easy or smart way to fix it. I can only think of a step-by-step calculation to get it.

DupColor = var _count1=COUNTROWS(FILTER('Table',[Product]=EARLIER('Table'[Product])))
var _count2=COUNTROWS(FILTER('Table',[Color]=EARLIER('Table'[Color])&&[Product]=EARLIER('Table'[Product])))
var _maxcount2=MAXX(FILTER('Table',[Product]=EARLIER('Table'[Product])),_count2)
return IF(_count1=_maxcount2,"Duplicated","Color")

DupSize = var _count1=COUNTROWS(FILTER('Table',[Product]=EARLIER('Table'[Product])))
var _count2=COUNTROWS(FILTER('Table',[Size]=EARLIER('Table'[Size])&&[Product]=EARLIER('Table'[Product])))
var _maxcount2=MAXX(FILTER('Table',[Product]=EARLIER('Table'[Product])),_count2)
return IF(_count1=_maxcount2,"Duplicated","Size")

DupSlim = var _count1=COUNTROWS(FILTER('Table',[Product]=EARLIER('Table'[Product])))
var _count2=COUNTROWS(FILTER('Table',[Slim]=EARLIER('Table'[Slim])&&[Product]=EARLIER('Table'[Product])))
var _maxcount2=MAXX(FILTER('Table',[Product]=EARLIER('Table'[Product])),_count2)
return IF(_count1=_maxcount2,"Duplicated","Slim")

DiffColumn = IF([DupColor]="Duplicated",IF([DupSize]="Duplicated",IF([DupSlim]="Duplicated",BLANK(),[DupSlim]),[DupSize]),[DupColor])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marcelmunk]

Thanks for the answer, @Anonymous !

I wander if there's a more elegant (and general) solution.