Reputation: 49
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
Reputation: 2967
Try this in the query editor.
Remove Duplicates
Upvotes: 1
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