Forum Discussion
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 ...
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
Sample File: https://drive.google.com/file/d/1Pd7K4vw-cZzD1CG-ITmUrfXiO8e3nSXt/view?usp=drive_link
I need the ID column in the Append table to remove duplicates while keeping all the nulls.
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
CombinedTIn 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.
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 CombinedTThank you. Another issue is in my actual file the column known as "ID" is actually named "CER #". How would this change the code?