Forum Discussion

andybamber's avatar
andybamber
Helper III
4 years ago
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...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"
rohit_singh's avatar
rohit_singh
Solution Sage
4 years ago

Hi andybamber ,

You just need to add an additional line to sort data before you group and concatenate :

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]),
    #"Sorted Rows" = Table.Sort(Source,{{"Code", Order.Ascending},{"Mail", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Code", type text}, {"Mail", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type",{"Code"},{{"Concat Mail", each Text.Combine([Mail],";"), type text}})
in
    #"Grouped Rows"

 

#"Sorted Rows" = Table.Sort(Source,{{"Code", Order.Ascending},{"Mail", Order.Ascending}})

 

This will sort your data in ascending order using the code column first, and then the mail column.

The result is this :

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! ðŸ™‚