Forum Discussion
Populate group with most occurring address
I have a dataset that contains many lines of duplicative data with varying differences and need to normalize the list so the most frequent address shows for the group.
Example:
| Name | Group ID | Address | Normalized Address |
| John Doe | 1 | 123 Broadway | |
| John Doe | 1 | 123 Broadway | |
| John Doe | 1 | 459 Park Place | |
| John Doe | 1 | 123 Broadway | |
| Jane Doe | 2 | 456 Lexington |
I want to be able to populate the 'Normalized Address' column with "123 Broadway" for all lines associated with Group ID 1 and "456 Lexington" for Group ID 2
Desired output:
| Name | Group ID | Address | Normalized Address |
| John Doe | 1 | 123 Broadway | 123 Broadway |
| John Doe | 1 | 123 Broadway | 123 Broadway |
| John Doe | 1 | 459 Park Place | 123 Broadway |
| John Doe | 1 | 123 Broadway | 123 Broadway |
| Jane Doe | 2 | 456 Lexington | 456 Lexington |
Any help is much apprecaited!
Hi, Anonymous , you might want to try this,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyBGEjYwWnovzElPLESqVYHZLlTUwtFQISi7IVAnISk1OJMyExLxUqbwQ2wUzBJ7UiMy+9JD9PKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Group ID" = _t, Address = _t]), #"Added Custom" = Table.AddColumn(Source, "Normalized Address", each List.Mode(Table.Group(Source, {"Group ID"}, {{"All", each _}}){[Group ID=[Group ID]]}[All][Address])) in #"Added Custom"
2 Replies
CNENFRNLCommunity Champion
Hi, Anonymous , you might want to try this,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lEyBGEjYwWnovzElPLESqVYHZLlTUwtFQISi7IVAnISk1OJMyExLxUqbwQ2wUzBJ7UiMy+9JD9PKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Group ID" = _t, Address = _t]), #"Added Custom" = Table.AddColumn(Source, "Normalized Address", each List.Mode(Table.Group(Source, {"Group ID"}, {{"All", each _}}){[Group ID=[Group ID]]}[All][Address])) in #"Added Custom"CNENFRNL Thank you so much! This works perfectly but it takes a long time to execute and I need to run this across many different fields. Any suggestions on how to get this to run faster?