none
String_Agg or Listagg function RRS feed

  • Question

  • The Split Column capability is nice. Is there an easy way in Power Query to do the reverse? SQL Server has the String_Agg function which does it, but how to replicate this in PQ? Really looking for a way to do something like example "E. Generate list of emails per towns" in the SQL documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

    Tuesday, April 18, 2017 7:01 PM

Answers

  • You can use the "Group By"  option on the Transform tab:

    Group on your GroupField (e.g. Town) and choose a sum operation for the email field.
    Of course this will create an error, but you get your base code to adjust to e.g.

    = Table.Group(#"Changed Type", {"GroupField"}, {{"Emails", each Text.Combine([Email],";"), type text}})
    In this example #"Changed Type" is the name of the previous step and "Emails" is the name I entered in Group By as the column name for the aggregated Email values.

    • Marked as answer by Rich Bulan Wednesday, April 19, 2017 3:28 PM
    Tuesday, April 18, 2017 7:52 PM

All replies

  • You can use the "Group By"  option on the Transform tab:

    Group on your GroupField (e.g. Town) and choose a sum operation for the email field.
    Of course this will create an error, but you get your base code to adjust to e.g.

    = Table.Group(#"Changed Type", {"GroupField"}, {{"Emails", each Text.Combine([Email],";"), type text}})
    In this example #"Changed Type" is the name of the previous step and "Emails" is the name I entered in Group By as the column name for the aggregated Email values.

    • Marked as answer by Rich Bulan Wednesday, April 19, 2017 3:28 PM
    Tuesday, April 18, 2017 7:52 PM
  • Worked great. Thank you. Below is the full code of the solution from the Advanced Editor. I had two columns in my sample data set, Table1 (Town & Email):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Town", type text}, {"Email", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Town"}, {{"Emails", each Text.Combine([Email],";"), type text}})
    in
        #"Grouped Rows"

    Wednesday, April 19, 2017 3:30 PM