Group By

[AndreaLH]

Good day,

Please be gentle, I am know enough to seem like I know what I am doing but I don't.

Below is the format of the data I am working with. I need to aggregate by Claim Number (1 row per claim number). In Power Query, I used Advanced and Group By Claim Number.

For my new columns I did as follows:

• For numeric columns used the Sum operation on each individually
• Once all of those were done I created a column "Aggregate" and selected the All Rows Operation

This is where I am stuck.

The summed rows are fine.

I have the Aggregate column with the resulting tables.

1. For some of the text columns, I need them aggregated eliminating duplicates. For example, Member Name would show the name once.
2. For other text columns, I need to merge the rows (the delimiter doesn't matter but comma works)

I realize I need to create a custom column for these rows but everything I have tried results in an error in each row of the resulting column. These are what I have tried:

1. Text.Combine(List.Distinct([Member Name]), ", ")

2. Text.Combine(List.Distinct(List.Transform([All Rows], each [Member Name])), ", ")

3. Text.Combine(List.Distinct(List.Transform([All Rows], each Record.Field(_, "Member Name"))), ", ")

Here are my questions:

1. Once the aggregate is complete on Claim Number and the numeric columns are summed. Do I expand or aggregate the Aggregate column (seem exand because aggregate wants to count the member name but I could be wrong)
2. When I did Expand, I ended up pretty much where I started with two rows for each Claim Number.
3. Custom columns resulted in error.

I have been banging my head on this since last night and any help would be appreciated.

Sample Data:

Member Name	Member ID	Claim Number	Claim Status	Line of Business	Provider Name	Provider Tax ID	National Provider Identifier	Provider Status	Billed Amount	Amount Paid	Interest Amount Paid	MIPS Bonus Amount Paid	Denial Reason Code or Description	CPT/revenue/HCPCS Code
Andrea Hazzard	123454667	99233657	Paid	Medicaid	Dr. Bill	123	78	Par	10	7	0	0	Approved	V2020
Andrea Hazzard	123454667	99233657	Denied	Medicaid	Dr. Bill	123	78	Par	5	2	0	0	Denied	V2300
Michael Johnson	235689871	99233658	Paid	Medicaid	Dr. Sam	456	787	Nonpar	12	10	0	0	Approved	V2020
Michael Johnson	235689871	99233658	Paid	Medicaid	Dr. Sam	456	787	Nonpar	10	8	0	0	Approved	V2300

Images of what my results have been in Power Query:

Result of above:

Any help on how to move forward would be hugely appreciated.

[v-priyankata]

Hi @AndreaLH 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @AndreaLH 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[SundarRaj · Accepted solution]

Hi @AndreaLH , is this what you are looking for? 

If this is similar to what you'd like to achieve, I'll share a snippet of the M code for reference.

In this partcular scenario, I've taken just the Member name column for the text column. In case you wish to take more columns and combine them, I'll leave the code for that below. (Use another nested list in Grouping Function and use the below function)

List.Transform(Table.ToColumns(Table.RemoveColumns(_,NumericCols)), each Text.Combine(_,",")). Thanks

[ZhangKun]

Actually, you have done it right, you just put the code in the wrong place.

Table.Group(
        your_table, 
        {"Claim Number"}, {
        {"Billed_Amount", each List.Sum([Billed Amount]), type nullable number}, 
        {"Amount_Paid", each List.Sum([Amount Paid]), type nullable number}, 
        {"Interest_Amount_Paid", each List.Sum([Interest Amount Paid]), type nullable number}, 
        {"MIPS_Bonus_Amount_Paid", each List.Sum([MIPS Bonus Amount Paid]), type nullable number}, 
        // Text.Combine(List.Distinct([Member Name]), ", ")
        {"Aggregate", each Text.Combine(List.Distinct([Member Name]), ", "), type table}
    })

If you are adding a new column, you should use:

Text.Combine(List.Distinct([Aggregate][Member Name]), ", ")

[AndreaLH]

This is more understandable for me so thank you. The M code was automatically generated as I went through

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Member Name", type text}, {"Member’s Humana ID", Int64.Type}, {"Claim Number", Int64.Type}, {"Claim Status", type text}, {"Line of Business", type text}, {"Provider Name", type text}, {"Provider Tax ID", Int64.Type}, {"National Provider Identifier", Int64.Type}, {"Provider Status", type text}, {"Billed Amount", Int64.Type}, {"Amount Paid", Int64.Type}, {"Interest Amount Paid", Int64.Type}, {"MIPS Bonus Amount Paid", Int64.Type}, {"Denial Reason Code or Description", type text}, {"CPT/revenue/HCPCS Code", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Claim Number"}, {{"Billed Amount Total", each List.Sum([Billed Amount]), type nullable number}, {"Amount Paid Total", each List.Sum([Amount Paid]), type nullable number}, {"Interest Amount Total", each List.Sum([Interest Amount Paid]), type nullable number}, {"MIP Bonus Amount Paid Total", each List.Sum([MIPS Bonus Amount Paid]), type nullable number}, {"Aggregate", each _, type table [Member Name=nullable text, #"Member’s Humana ID"=nullable number, Claim Number=nullable number, Claim Status=nullable text, Line of Business=nullable text, Provider Name=nullable text, Provider Tax ID=nullable number, National Provider Identifier=nullable number, Provider Status=nullable text, Billed Amount=nullable number, Amount Paid=nullable number, Interest Amount Paid=nullable number, MIPS Bonus Amount Paid=nullable number, Denial Reason Code or Description=nullable text, #"CPT/revenue/HCPCS Code"=nullable text]}}),
    #"Expanded Aggregate" = Table.ExpandTableColumn(#"Grouped Rows", "Aggregate", {"Member Name", "Member’s Humana ID", "Claim Number", "Claim Status", "Line of Business", "Provider Name", "Provider Tax ID", "National Provider Identifier", "Provider Status", "Billed Amount", "Amount Paid", "Interest Amount Paid", "MIPS Bonus Amount Paid", "Denial Reason Code or Description", "CPT/revenue/HCPCS Code"}, {"Member Name", "Member’s Humana ID", "Claim Number.1", "Claim Status", "Line of Business", "Provider Name", "Provider Tax ID", "National Provider Identifier", "Provider Status", "Billed Amount", "Amount Paid", "Interest Amount Paid", "MIPS Bonus Amount Paid", "Denial Reason Code or Description", "CPT/revenue/HCPCS Code"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Aggregate", "New Member Name", each Text.Combine(List.Distinct(List.Transform([All Rows], each Record.Field(_, "Member Name"))), ", "))
in
    #"Added Custom"

[v-priyankata]

Hi @AndreaLH 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[dufoq3]

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

[AndreaLH]

Yes, exactly this. As a newbie, is what you provided M Code that needs to be pasted in? I have more columns in the actual data and I am certainly not even beginner level in M Code but I may be able to add the columns based on how the sample columns were handled.

[dufoq3]

Read note below my post.