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"