Forum Discussion
Solved
Identifying Duplicates
We are currently trying to replicate some reporting we already do in excel and use Power BI instead and have hit a snag. Our data contains 2 identifying markers which we call Home and Away and...
Hi rmba22875 ,
Here are the steps you can follow：
1. In Power Query -- Add Column – Index Column – From 1.
2. Create calculated column.
Column 2 = var _right= VALUE(RIGHT([Column],3)) var _left= VALUE(MID([Column],3,3)) return IF( _left>_right, RIGHT([Column],5)&"/"&LEFT([Column],5), LEFT([Column],5)&"/"&RIGHT([Column],5))Column 3 = var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Index]<>EARLIER('Table'[Index])),"1",[Column 2]) return IF( 'Table'[Column 2] in _select ,"Duplicate","")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi rmba22875 ,
Here are the steps you can follow：
1. In Power Query -- Add Column – Index Column – From 1.
2. Create calculated column.
Column 2 =
var _right=
VALUE(RIGHT([Column],3))
var _left=
VALUE(MID([Column],3,3))
return
IF(
_left>_right,
RIGHT([Column],5)&"/"&LEFT([Column],5),
LEFT([Column],5)&"/"&RIGHT([Column],5))
Column 3 =
var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Index]<>EARLIER('Table'[Index])),"1",[Column 2])
return
IF(
'Table'[Column 2] in _select ,"Duplicate","")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly