Creating a summarized table to remove duplicates

[Anonymous]

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 in the table below

Current data
Address Number	Alpha Name	Electornic Address
1	ABC	ABC@GMAIL.COM
1	ABC
2	cde	CDE@GMAIL.COM
3	fgh
3	fgh	FGH@GMAIL.COM
4	hij
New Data
Address Number	Alpha Name	Electornic Address
1	ABC	ABC@GMAIL.COM
2	cde	CDE@GMAIL.COM
3	fgh	FGH@GMAIL.COM
4	hij

[CNENFRNL]

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	ABC@GMAIL.COM
1	ABC
1	ABC	another@hotmail.com
2	cde	CDE@GMAIL.COM
3	fgh
3	fgh	FGH@GMAIL.COM
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"

[ValtteriN · Accepted solution]

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/