Forum Discussion
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 in the table below
| Current data | ||
| Address Number | Alpha Name | Electornic Address |
| 1 | ABC | [email protected] |
| 1 | ABC | |
| 2 | cde | [email protected] |
| 3 | fgh | |
| 3 | fgh | [email protected] |
| 4 | hij | |
| New Data | ||
| Address Number | Alpha Name | Electornic Address |
| 1 | ABC | [email protected] |
| 2 | cde | [email protected] |
| 3 | fgh | [email protected] |
| 4 | hij |
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/
2 Replies
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"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/
