CODEWITHSUNDEEP
powerbidaxpowerquerym
deoos
deoos

Reputation: 49

Power BI. Remove duplicates but keep null values

In Power BI, I need to remove duplicates of a column but keep the null values as they are 'pending'. Is there a way I can do it with DAX or the Query Editor?

Upvotes: 2

Views: 8731

Answers (2)

Marco Vos
Marco Vos

Reputation: 2967

Try this in the query editor.

  1. Add an index column (Add Column tab > Index Column)
  2. Add a Custom Column with this formula ([Test] is your original column with nulls and duplicates.

enter image description here

  1. Right-click the latest column [Temp] and select Remove Duplicates
  2. Remove [Index] and [Temp] columns

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40304

Filter the table in two ways, without nulls and only nulls.

On the table without nulls, remove duplicates. Home > Remove Rows > Remove Duplicates

Append the null rows to this table.

The M code will look like this:

let
    Source = <Data source or previous step reference here>,
    AllNulls = Table.SelectRows(Source, each ([Column1] = null)),
    NoNulls = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Removed Duplicates" = Table.Distinct(NoNulls),
    #"Appended Query" = Table.Combine({#"Removed Duplicates", AllNulls})
in
    #"Appended Query"

Upvotes: 6

Related Questions