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 do this within an Append table. The IDs with nulls are coming from a budget table.
Thanks!
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
9 Replies
Hi, provide sample data and expected result please.
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 CombinedT
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 xSource
De-Duped
