Group By: Flatten single text data element with delimiter separator

Question

Hello,&nbsp;I thought I had figured this out once before however I'm stumped... I have data that looks something like this&nbsp;Financial AcctDetail AcctValuePayableTrade5PayableFX10ReceivableTrade111PendingFI20PendingEquity25PendingDerivative50&nbsp;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.&nbsp;Financial AcctDetail AccountValuePayableTrade, FX15ReceivableTrade111PendingFI, Equity, Derivative95&nbsp;Thanks,Brian

imkef · Accepted Answer

Hi&nbsp;&nbsp;fox252&nbsp;,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:
&nbsp;
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"
&nbsp;

Forum Discussion

Group By: Flatten single text data element with delimiter separator

1 Reply

Related content

Escape row delimiter within quotes

Flatten nested JSON in KQL DB

decompress, skip lines, delimit

Separating Delimited column with multiple delimiters

Null values in flattened JSON

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

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