Forum Discussion

rmba22875's avatar
rmba22875
Frequent Visitor
3 years ago
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...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

     

YukiK's avatar
YukiK
Impactful Individual
3 years ago

This should be done in Power Query if you're looking to remove duplicates. Go to query editor and you'll a plenty of data manipulation options there

 

If not, would you elaborate what you're trying to achieve? Are you trying to add a column that identifies duplicates?

rmba22875's avatar
rmba22875
Frequent Visitor
3 years ago

Yes we need to apply a column that identifies the duplicates. This is so we can then manipulate the data as we need too as sometimes we need to look at it as a whole and other times without the duplicates.