Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a summarized table to remove duplicates

Im looking to create a new table and to remove duplicate entries where it is zero but where there is not duplicate to still leave the empty field in the table based on email address.    As desribed...
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi Anonymous ,

    You can try using SUMMARIZE:

    SUMMARIZE('Table (7)','Table (7)'[Address Number],'Table (7)'[Alpha Name],"Email",MAX('Table (7)'[Electornic Address]))

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/



CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago

Resort to PQ for such data extraction/data shaping tasks; so that you can easily handle such a case,

 

Address Number Alpha Name Electornic Address
1 ABC [email protected]
1 ABC  
1 ABC [email protected]
2 cde [email protected]
3 fgh  
3 fgh [email protected]
4 hij  

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0coaQDu6+jp4+es7+vkqxOshyaNzEvPySjNQih4z8ktzEzBy95PxcsAojoFxySiqQdHZxRTPMGCialp4BMwzBdXP3QFNqAhTNyMwCK40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Address Number" = _t, #"Alpha Name" = _t, #"Electornic Address" = _t]),

    #"Filtered Email" = Table.Group(Source, List.FirstN(Table.ColumnNames(Source),2), {"Grp", each Table.SelectRows(_, (r) => Text.Length(r[Electornic Address]) > 0)}),
    #"Expanded Aggr" = Table.ExpandTableColumn(#"Filtered Email", "Grp", {"Electornic Address"})
in
    #"Expanded Aggr"