Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove duplicates while retaining null/blank rows in a column

Need to remove duplicate values from a column that had a lot of null values too. Need to retain the null rows. Tried using query editor's remove duplicate rows function, but it removes all null valu...
Need Help
  • ziying35's avatar
    ziying35
    6 years ago

    Hi, Anonymous 

     

    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WSlSyUko2NDJV0lFKAjIrKlKUanVgwiZQ0eLEdLhooiFMbTFcMK80JwcmVoxNJValSIZWGRlClaan58BF0+AOSM/G1J+ajc2q3JxiTKW5SEqT4Y4qT1WqjQUA",BinaryEncoding.Base64),Compression.Deflate))),
    result = Table.FromRows(List.Distinct(Table.ToRows(Source),each if _{0}=null then Text.NewGuid() else _{0}))
in
    result

    If my code can solve your problem, Mark it as a solution

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

1. Filter out all nulls from your source table and then run Remove duplicates

2. Filter out  all non-nulls from your original table

3. Combine 1 + 2.

 

Kind regards,

JB