Remove duplicates but keep nulls

[Anonymous]

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!

[ronrsnfld]

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

[dufoq3]

Hi, provide sample data and expected result please.

[Anonymous]

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.

[dufoq3]

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]

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 · Accepted solution]

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]

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

[Anonymous]

I was able to find a work around by changing the column name to just "CER".

[dufoq3]

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