Power BI - Power Query Editor: Remove All Duplicates (Don't leave any rows that were part of the duplicate)

0
votes

So, I know how to remove duplicates which leave one row behind. What I want to do is remove all of the rows associated with a duplicate, because we don't know which of the duplicates we want to keep, and for our purposes therefore don't want any of them in our table. There are only two columns. One column contains the duplicates. The second has unique values per duplicate, but we don't want any of them to remain.

Thank you.

1
powerbidaxpowerquery

1 Answers

1
votes

Here is a possible workaround. Use Table.Group to count the duplication, then retain only unique entries using Table.SelectRows.

let
    Source = Table.FromRecords({
        [a = "A", b = "a"],  // < duplicated
        [a = "B", b = "a"],
        [a = "A", b = "a"]   // < duplicated
    })
in
    Table.SelectRows(
        Table.Group(Source, {"a", "b"}, {"Count", Table.RowCount}),
        each [Count] = 1
    )

/*
 * Output
 *
 *  a   b  Count
 * --- --- -----
 *  B   a    1
 */