How to remove duplicate Only if column is X

[JBDelvaux]

Greetings,

I have the following tables. I would like to remove rows if WHStatus (green) is DPi OR DPa. And row would be duplicate if all columns in red are the same.

So if I have 6 columns wiht WHStatus of TPa, they are not duplicate. They're separate data.

If I have 2 columns with DPi and the same piece/user, they are duplicate.

[AlienSx · Accepted solution]

hello, @JBDelvaux 

let
    Source = your_table,
    dp = {"DPi", "DPa"},
    dist = {"UserId", "WHStatus", "Journal", "Piece"},
    whs_groups = Table.Group(Source, "WHStatus", {{"all", each _}}),
    mod_tbl = 
        Table.AddColumn(
            whs_groups, "mod", 
            (x) => 
                if List.Contains(dp, x[WHStatus])
                then Table.Distinct(x[all], dist)
                else x[all]
        ),
    z = Table.Combine(mod_tbl[mod])
    
in
    z

[lbendlin]

I was more thinking along the lines of 

- cutting the table into two parts based on X

- deduplicate the affected part

- append them back together

[AlienSx]

@lbendlin no doubt this works either. I wonder what is faster on hudge dataset.

[lbendlin]

There's always Table.Buffer 🙂

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...