Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Remove duplicates but keep nulls

I am having an issue while attempting to remove duplicates but keeping nulls. The ID column has both duplicates and nulls. I need to remove the duplicate values but keep the null IDs. I am trying to ...
Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    1 year ago
    let
    Source = Table.Combine({Master, Budget}),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    GroupedRows = Table.Group(ChangedType, {"ID"}, {{"T", each if [ID]{0} = null then _ else Table.FirstN(_, 1), type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT
ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

Since you chose not to share sample data, here is an example with a one column table of ID's.

You should be able to adapt it to your actual data set.

It makes use of the fact that List.Distinct has an optional Equation Criteria:

If ID is not the first column, change the Index {0} to reflect its location

 

let
    Source = Table.FromColumns(
        {{"ab","cd",null,null,"ef","ab", "gh", null,"ij"}},
         type table[ID=nullable text]),
         
    x =Table.FromRows(List.Distinct(Table.ToRows(Source),each if _{0}=null then Text.NewGuid() else _{0}))

in
    x

 

 

Source

 

De-Duped