Power BI. Remove duplicates but keep null values

Question

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?

Answer 1

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"

Answer 2

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.

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