[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UID", type text}, {"HH", type text}, {"Notes", type text}, {"Account", type text}, {"Fee", type number}, {"IMA Number", Int64.Type}, {"First", type text}, {"Middle", type text}, {"Last", type text}, {"Address", type text}, {"City", type text}, {"State", type text}, {"Zip", Int64.Type}, {"HOME Phone", type text}, {"CELL Phone", type text}, {"E-Mail Address", type text}, {"Address2", type text}, {"City3", type text}, {"State4", type text}, {"Zip5", Int64.Type}, {"2nd First", type text}, {"2nd Middle", type text}, {"2nd Last", type text}, {"2nd Address", type any}, {"2nd City", type any}, {"2nd State", type any}, {"2nd Zip", type any}, {"2nd HOME Phone", type any}, {"2nd CELL Phone", type text}, {"2nd E-Mail Address", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"HH"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "UID", each Table.Column([Count],"UID")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"UID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Notes", each Table.Column([Count],"Notes")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Notes", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Added Custom2" = Table.AddColumn(#"Extracted Values1", "Account", each Table.Column([Count],"Account")),
#"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"Account", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Fee", each Table.Column([Count],"Fee")),
#"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Fee", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom4" = Table.AddColumn(#"Extracted Values3", "IMA Number", each List.Distinct(Table.Column([Count],"IMA Number"))),
#"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"IMA Number", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom5" = Table.AddColumn(#"Extracted Values4", "First", each List.Distinct(Table.Column([Count],"First"))),
#"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"First", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Middle", each List.Distinct(Table.Column([Count],"Middle"))),
#"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Middle", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom7" = Table.AddColumn(#"Extracted Values6", "Last", each List.Distinct(Table.Column([Count],"Last"))),
#"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"Last", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom8" = Table.AddColumn(#"Extracted Values7", "Address", each List.Distinct(Table.Column([Count],"Address"))),
#"Extracted Values8" = Table.TransformColumns(#"Added Custom8", {"Address", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom9" = Table.AddColumn(#"Extracted Values8", "City", each List.Distinct(Table.Column([Count],"City"))),
#"Extracted Values9" = Table.TransformColumns(#"Added Custom9", {"City", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom10" = Table.AddColumn(#"Extracted Values9", "State", each List.Distinct(Table.Column([Count],"State"))),
#"Extracted Values10" = Table.TransformColumns(#"Added Custom10", {"State", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom11" = Table.AddColumn(#"Extracted Values10", "Zip", each List.Distinct(Table.Column([Count],"Zip"))),
#"Extracted Values11" = Table.TransformColumns(#"Added Custom11", {"Zip", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom12" = Table.AddColumn(#"Extracted Values11", "HOME Phone", each List.Distinct(Table.Column([Count],"HOME Phone"))),
#"Extracted Values12" = Table.TransformColumns(#"Added Custom12", {"HOME Phone", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom13" = Table.AddColumn(#"Extracted Values12", "CELL Phone", each List.Distinct(Table.Column([Count],"CELL Phone"))),
#"Extracted Values13" = Table.TransformColumns(#"Added Custom13", {"CELL Phone", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Added Custom14" = Table.AddColumn(#"Extracted Values13", "E-Mail Address", each List.Distinct(Table.Column([Count],"E-Mail Address"))),
#"Extracted Values14" = Table.TransformColumns(#"Added Custom14", {"E-Mail Address", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Reordered Columns" = Table.ReorderColumns(#"Extracted Values14",{"UID", "HH", "Count", "Notes", "Account", "Fee", "IMA Number", "First", "Middle", "Last", "Address", "City", "State", "Zip", "HOME Phone", "CELL Phone", "E-Mail Address"})
in
#"Reordered Columns"[/SIZE]