Forum Discussion
Solved
Group By - Text.Combine
Hello! I have attached a PBIX. I have a small data table which has a Code column and a Mail column For each of the Codes A0, B1, C2 there are the same 4 mail addresses a.com, b.com, c.com...
Yes, you will need to sort the second column - Then buffer the table and use buffered table in the Group by. If you don't use buffered table, you will have original sort order.
Below is revised query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRQ0lFK1EvOz1WK1YFyk1C5yajcFDjXyRCTm4zKTULlIixyNsLkJqFyk1G5CIucjYHcVAg3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Mail = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Mail", type text}}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Mail", Order.Ascending}})), #"Grouped Rows" = Table.Group(#"Sorted Rows",{"Code"},{{"Concat Mail", each Text.Combine([Mail],";"), type text}}) in #"Grouped Rows"
Yes, you will need to sort the second column - Then buffer the table and use buffered table in the Group by. If you don't use buffered table, you will have original sort order.
Below is revised query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRQ0lFK1EvOz1WK1YFyk1C5yajcFDjXyRCTm4zKTULlIixyNsLkJqFyk1G5CIucjYHcVAg3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Mail = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Mail", type text}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Mail", Order.Ascending}})),
#"Grouped Rows" = Table.Group(#"Sorted Rows",{"Code"},{{"Concat Mail", each Text.Combine([Mail],";"), type text}})
in
#"Grouped Rows"