Group By

Hi AndreaLH, like this?

All columns with words "Amount" or "Paid" in name are summed.

All other columns are concatenated with comma (but only their unique values)

Output (first few columns)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZA7C4MwFIX/imSWEhMT42hxKAVLoeAiDkEDBmwiKXTw1zfX9LVY2qHDCSePw3dPmgYVpndKRjs5z9L1KEYJoSlLOc+8z3NCKWdgj1LDbaV63QVbuk201eMYMn7NxPLOwQGGvRe+q5gmZ68KcjXBBKM2/p5dKqPVD3TmRd7gz3xNKA7oSneDVGO0t4O5WAMByrjIRZa82GK190mevUsZX8Aw48GaKVQnj/4fu/9xAGCKNf7yAe0N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Member Name" = _t, #"Member ID" = _t, #"Claim Number" = _t, #"Claim Status" = _t, #"Line of Business" = _t, #"Provider Name" = _t, #"Provider Tax ID" = _t, #"National Provider Identifier" = _t, #"Provider Status" = _t, #"Billed Amount" = _t, #"Amount Paid" = _t, #"Interest Amount Paid" = _t, #"MIPS Bonus Amount Paid" = _t, #"Denial Reason Code or Description" = _t, #"CPT/revenue/HCPCS Code" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Member ID", Int64.Type}, {"Claim Number", Int64.Type}, {"Provider Tax ID", Int64.Type}, {"National Provider Identifier", Int64.Type}, {"Billed Amount", type number}, {"Amount Paid", type number}, {"Interest Amount Paid", type number}, {"MIPS Bonus Amount Paid", type number}}),
    Helper = [ sumCols = List.Select(Table.ColumnNames(ChangedType), each List.Contains({"amount", "paid"}, _, (x,y)=> Text.Contains(y, x, Comparer.OrdinalIgnoreCase))),
    otherCols = List.Difference(Table.ColumnNames(ChangedType), sumCols)
  ],
    StepBack = ChangedType,
    GroupedRows = Table.Group(StepBack, {"Claim Number"}, {{"All", each _, type table}, {"T", each 
        [ sum = List.Accumulate(Helper[sumCols], [], (st,cur)=> Record.AddField(st, cur, List.Sum(Table.Column(_, cur)))),
          other = List.Accumulate(Helper[otherCols], [], (st,cur)=> Record.AddField(st, cur, let a1 = Table.Column(_, cur) in if a1{0} is number then a1{0} else Text.Combine(List.Distinct(a1), ", "))),
          toTbl = Table.FromRecords({ sum & other })
        ][toTbl], type table}}),
    CombinedT = Value.ReplaceType(Table.ReorderColumns(Table.Combine(GroupedRows[T]), Table.ColumnNames(ChangedType)), Value.Type(ChangedType))
in
    CombinedT