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
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.
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?
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 CombinedTI was able to find a work around by changing the column name to just "CER".