Issue with duplicated Rows

[michal_boro]

Hello All,

I ve got a table wich look like this

Specialist Customer Service	Specialist Invoicing and Settlement
Specialist Customer Service	Specialist Order Data Management
Specialist Invoicing and Settlement	Specialist Customer Service
Specialist Invoicing and Settlement	Specialist Order Data Management
Specialist Order Data Management	Specialist Customer Service
Specialist Order Data Management	Specialist Invoicing and Settlement

What i need to do is remove one of duplicated pair.

As you can see as first row there is:

Column1:  Spec. Cust. Serv and Column2: Spec Inv. and Sett.

and also in row third there is te same pair but in different order 

Column1: Spec Inv. and Sett. and Column2:  Spec. Cust. Serv.

From my point of view those are the same data and one of them have to be removed (doesnt matter which one).

No idea how to do it. Can you help?

[AlienSx]

    add_col = Table.AddColumn(
        your_table, 
        "check", 
        each List.Sort(Record.FieldValues([[Column1], [Column2]]))
    ),
    distinct = Table.Distinct(add_col, "check"),
    rm_col = Table.RemoveColumns(distinct, "check")

[Anonymous · Accepted solution]

"Remov Dupliactes" is great, @AbhinavJoshi .
But removing the duplicate directly didn't provide @michal_boro  with the expected result.
Pease allow me to add that below are the detailed steps:

1.Create two custom columns, one is the result of column 1 merging column 2, and the other is the result of column 2 merging column 1.

2.Select C1C2 and C2C1 at the same time, then click "Unpivot Columns".

3.Select the Value column and remove duplicates.

4.Remove the Attribute column and the Value column.

5.Select the Column1 and the Column2 and remove duplicates again.

6.Here's your final result.

I upload the attachment for your reference. You can download it and go to Power Query Editor to check it.

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.

[michal_boro]

You are amazing! It`s working. Thanks a loooot! 

[michal_boro]

Nope, it`s gonna delete "random" row from column 2 also, i need to be sure that right pair stays.

[AbhinavJoshi]

Hello @michal_boro. 

You can use the remove duplicates in Power Query Editor. Select the column, here for example col1, right click and remove duplicates.

I hope it helps.

Thank you,