topic Creating a summarized table to remove duplicates in DAX Commands and Tips

Creating a summarized table to remove duplicates

Anonymous — Thu, 19 May 2022 06:24:50 GMT

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

Re: Creating a summarized table to remove duplicates

ValtteriN — Thu, 19 May 2022 07:16:50 GMT

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/

Re: Creating a summarized table to remove duplicates

CNENFRNL — Thu, 19 May 2022 08:28:01 GMT

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"