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
dufoq3's avatar
dufoq3
Community Champion
1 year ago

Output

 

Select Append1 query. Open advanced editor. Replace whole code with this one:

let
    Source = Table.Combine({Master, Budget}),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    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
Anonymous's avatar
Anonymous
Not applicable
1 year ago

In my actual file (not the test), the ID column is text. It looks like this "123.456.789". I believe this is giving the code issues.

  • dufoq3's avatar
    dufoq3
    Community Champion
    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you. Another issue is in my actual file the column known as "ID" is actually named "CER #". How would this change the code?

      • dufoq3's avatar
        dufoq3
        Community Champion
        1 year ago

        If you need further assistance, provide sample data again with full description that covers your issue and don't forget to provide also expected result based on sample data.

         

         

        let
    Source = Table.Combine({Master, Budget}),
    ChangedType = Table.TransformColumnTypes(Source,{{"CER #", type text}}),
    GroupedRows = Table.Group(ChangedType, {"CER #"}, {{"T", each if [#"CER #"]{0} = null then _ else Table.FirstN(_, 1), type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT