CODEWITHSUNDEEP
excelpowerquerym
MrDark
MrDark

Reputation: 67

How do I remove rows with duplicate values except those that contains null end empty values?

I want to use power query to remove rows with duplicate values in a column containing e-mail addresses. But I do not want to remove rows with duplicate null values or duplicate empty cells.

How do I accomplish that?

Upvotes: 0

Views: 2626

Answers (2)

MrDark
MrDark

Reputation: 67

I came up with a different solution to the problem.

  1. Create a custom column with values from a index column if the email column was emtpty/null but from the email column if it was not empty/null.
  2. Remove duplicate rows from the newly created column.
  3. Remove the custom column.

Worked fine with me!

I thought that I should share the solution if anyone else can be helped by it.

Upvotes: 1

MarcelBeug
MarcelBeug

Reputation: 2967

Split the table in records with nulls/"" and other records. Remove duplicate emails from the latter table. Append both tables.

let
    Source = Input,
    Empties = Table.SelectRows(Source, each [Email] = null or [Email] = ""),
    Others = Table.SelectRows(Source, each [Email] <> null and [Email] <> ""),
    RemovedDuplicateEmails = Table.Distinct(Others, {"Email"}),
    Appended = Table.Combine({RemovedDuplicateEmails,Empties})
in
    Appended

Upvotes: 2

Related Questions