Forum Discussion

v_mark's avatar
v_mark
Helper V
5 years ago
Solved

Identify Duplicates based on multiple conditions/columns

I have an entire dataset which has multiple entries that has duplicates.    So far this is how it looks like. The 2nd row has a duplicate 8 Characters on the top I need to identify in any ev...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, v_mark 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column with the following m codes.

     

    let 
device=[Device],
status=[Status],
state=[State],
sn=[SN],
tab = Table.SelectRows(#"Renamed Columns",each 
[Device]=device and 
[Status]=status and 
[State]=state and (
Text.EndsWith([SN],sn) or Text.EndsWith(sn,[SN])) ),
re = Table.RowCount(tab)
in 
if re>=2 
then 
"duplicate"
else null

     

     

    Result:

     

    Best Regards

    Allan

     

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

     

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, v_mark 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

 

You may create a custom column with the following m codes.

 

let 
device=[Device],
status=[Status],
state=[State],
sn=[SN],
tab = Table.SelectRows(#"Renamed Columns",each 
[Device]=device and 
[Status]=status and 
[State]=state and (
Text.EndsWith([SN],sn) or Text.EndsWith(sn,[SN])) ),
re = Table.RowCount(tab)
in 
if re>=2 
then 
"duplicate"
else null

 

 

Result:

 

Best Regards

Allan

 

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