Remove duplicates while retaining null/blank rows in a column

Hi Anonymous ,

If you want to remove duplicate rows from null-blank rows and keep all null rows, we can add a step to meet your requirement:

SelectDesireTable = Table.Combine({Table.SelectRows(NameOfYourLastStep, each [Column1] = null),Table.Distinct(Table.SelectRows(NameOfYourLastStep, each [Column1] <> null), {"Column1"})})

If the distinct logic depens on multi columns

SelectDesireTable = Table.Combine({
        Table.SelectRows(NameOfYourLastStep, each [Column1] = null and [Column2] = null),
        Table.Distinct(Table.SelectRows(NameOfYourLastStep, each [Column1] <> null or [Column2] <> null), {"Column1","Column2"})})

All queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcc3DsAwDATBv7BW41w75+xO0P+/4TULdgZugFvvJRInJSoJzkvMq9FoJbwWnVbK6zFYjZi0Mt6MxWrFppXzdhxWJy4tzrf/W3BuPBLCCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    SelectDesireTable = Table.Combine({Table.SelectRows(NameOfYourLastStep, each [Column1] = null),Table.Distinct(Table.SelectRows(NameOfYourLastStep, each [Column1] <> null), {"Column1"})})
in
    SelectDesireTable

If you want to keep the order, we can create a index column first, then create a custom column using following formula:

let c = _, 
temp = Table.SelectRows(#"Added Index",each [Column1] = c[Column1]),
rowcount =  Table.RowCount(temp)
in if rowcount=1 or [Column1]=null then [Index] else Table.Max(temp,"Index")[Index]

If you want to remove depends on multi columns:

let c = _, 
temp = Table.SelectRows(#"Added Index",each [Column1] = c[Column1] and [Column2] = c[Column2]),
rowcount =  Table.RowCount(temp)
in if rowcount=1 or ([Column1]=null and [Column2]=null) then [Index] else Table.Max(temp,"Index")[Index]

All the queries are here (Table (2) in sample file) :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcc3DsAwDATBv7BW41w75+xO0P+/4TULdgZugFvvJRInJSoJzkvMq9FoJbwWnVbK6zFYjZi0Mt6MxWrFppXzdhxWJy4tzrf/W3BuPBLCCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    NameOfYourLastStep = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Index" = Table.AddIndexColumn(NameOfYourLastStep, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let c = _, 
temp = Table.SelectRows(#"Added Index",each [Column1] = c[Column1]),
rowcount =  Table.RowCount(temp)
in if rowcount=1 or [Column1]=null then [Index] else Table.Max(temp,"Index")[Index]),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Index", "Custom"})
in
    #"Removed Columns"

After remove the duplicate rows depends on custom column and the additional column, we can get the desire result:

If it doesn't meet your requirement, Could you please show the exact expected result based on the tables that we have shared?

By the way, PBIX file as attached.

Best regards,