Populate group with most occurring address

[Anonymous]

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!

[CNENFRNL · Accepted solution]

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"

[Anonymous]

@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?