How to remove duplicates based on multiple criteria

[Mark1970]

I have a table with the following data from which I want to keep the green records and remove the yellow ones. Should I do this in powerquery or stick to Dax in power BI. I am using the table in Power BI for a dashboard.

[BA_Pete · Accepted solution]

Hi @Mark1970 ,

As this is a data structure operation, I'd recommend doing it in Power Query.

I'm assuming the filter logic is "filter out any row with no [WorkCenterCode], [Year], and [Week] value, unless it's the only [Order-Opr] value". If this is correct, then try this example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDc10DU0NFDSUYKiWB2EuBFI3NwARBoZGJlAKUMjTEVYNBuDxC3MTNE0GxOjyARTEcyGWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order-Opr" = _t, WorkCenterCode = _t, Year = _t, Week = _t]),
    repBlankNull = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"WorkCenterCode", "Year", "Week"}),

// Relevant steps from here =====>
    groupOrderOpr = Table.Group(repBlankNull, {"Order-Opr"}, {{"count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [#"Order-Opr"=nullable text, WorkCenterCode=nullable text, Year=nullable text, Week=nullable text]}}),
    expandData = Table.ExpandTableColumn(groupOrderOpr, "data", {"WorkCenterCode", "Year", "Week"}, {"WorkCenterCode", "Year", "Week"}),
    filterRows = Table.SelectRows(expandData, each [count] = 1 or ([count] > 1 and not ([WorkCenterCode] = null and [Year] = null and [Week] = null)))
    
in
    filterRows

Summary:

groupOrderOpr = Group the table on the [Ordedr-Opr] column, adding a count column and an All Rows column as aggregates.

expandData = Expand your original columns back out of the nested tables.

filterRows = Apply a logical filter, using the new count column we just created to ensure single-row values are kept even if target fields are null.

The example code turns this:

...into this:

Pete

[Mark1970]

Thanks!