CODEWITHSUNDEEP
powerbidaxpowerquery
Brian
Brian

Reputation: 83

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

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.

Upvotes: 0

Views: 1349

Answers (1)

Kosuke Sakai
Kosuke Sakai

Reputation: 2411

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
 */

Upvotes: 1

Related Questions