How to remove duplicates based on multiple criteria

Question

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.&nbsp;

ba_pete · Accepted Answer

Hi&nbsp;Mark1970&nbsp;,
&nbsp;
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&nbsp;[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 =====&gt;
    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] &gt; 1 and not ([WorkCenterCode] = null and [Year] = null and [Week] = null)))
    
in
    filterRows
&nbsp;
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.
&nbsp;
The example code turns this:

&nbsp;
...into this:

&nbsp;
Pete

Forum Discussion

Related content

Sequential Order column based on multiple criteria

DAX FILTER with multiple criteria

Remove duplicates based on multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria