Group By: Flatten single text data element with delimiter separator

[fox252]

Hello,

I thought I had figured this out once before however I'm stumped... I have data that looks something like this

Financial Acct	Detail Acct	Value
Payable	Trade	5
Payable	FX	10
Receivable	Trade	111
Pending	FI	20
Pending	Equity	25
Pending	Derivative	50

I am using "Group By" based on {Financial Acct}. I am summing {Value}. What I'm struggling with is how to flatten the {Detail Acct} field into a single row based on the grouping. Desired output below. I tried using "All Rows" which provides a table but I'm not sure how to extract the values I want with a delimiter.

Financial Acct	Detail Account	Value
Payable	Trade, FX	15
Receivable	Trade	111
Pending	FI, Equity, Derivative	95

Thanks,

Brian

[ImkeF · Accepted solution]

Hi  @fox252 ,
yes, "All Rows" is the way to go. You can add a column with this formula then to retrieve the concatenated fields:

Text.Combine([All][Detail Acct], ", ")

Please check out this solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkisTEzKSVXSUQopSkwB0aZKsTrI4m4RQMLQACwalJqcmlmGpsHQ0BCiJTUvJTMvHaTFE0gYGaCJuhaWZpZUgmRM0WRcUouAppZkloHtB+qLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Financial Acct" = _t, #"Detail Acct" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Financial Acct", type text}, {"Detail Acct", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Financial Acct"}, {{"SumValue", each List.Sum([Value]), type nullable number}, {"All", each _, type table [Financial Acct=nullable text, Detail Acct=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Detail Account", each Text.Combine([All][Detail Acct], ", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"})
in
    #"Removed Columns"