Aggregate Text Values Using Group By in Power Query

Most users know the Group By function in Power Query. It allows you to summarize data and aggregate underlying values. The standard aggregations are Average, Median, Min, Max, Count Rows, Count Distinct Rows and All Rows. Most of these are aimed at numbers. Power Query can however perform different operations more relevant to text. In this post you learn how to group your data and concatenate the underlying text values into a single cell.


Imagine below data set. Your goal is to summarize the data, so that only a single row is available for each product. Also, the total sales for this product should be available. This can be done using the UI (user interface). The challenge is to also add the underlying names of the sellers into a column. We will have to write some custom code here. Let’s get going!

Data set for grouping

The first steps can be done using the user interface. Let’s summarize the data by Product and Sum the underlying sales. To do that:

  • Go to the Home Tab -> Click Group By
Using the Group By functionality

In the Pop-up screen:

  • Click Advanced
  • Group By -> Product
  • In the aggregation section, add a new column name called ‘Sales’, set the operation to Sum and select Column ‘Sales’.

So far everything can be done by the user interface. The challenge left is to add a column containing all the seller names. The user interface does not provide the right code. Yet it does give an indication of how to write an argument for the Table.Group operation. Therefore it’s comfortable to add a ‘dummy’ aggregation, just so the setup is almost done. What you can do is:

  • Add another aggregation with the name ‘Sellers’, use the Sum Operation and name the column ‘Seller’.
  • Click OK
Group By menu to specify aggregations

The data is now grouped by Product, and has added the underlying sales. Notice that the last column contains an error. This error occurs since we told Power Query to perform a SUM operation on text values. Clearly, the SUM operation only works on numbers.

Error message when summing text values

Yet for our purposes, this setup is great. We can now correct the wrong formula without having to write everything from scratch.

How does Table.Group Work

Before we can change the code, it’s good to have a look at how Power Query groups data. When grouping data, Power Query uses the Table.Group formula. You can read more on this here.

The Table.Group formula takes three variables. It needs:

  1. a table. In this case the step #”Changed Type” refers to a table.
  2. the key columns. These columns specify the columns to group the data by. In this case the column named “Product” is the key to group the data by.
  3. aggregated columns as list. The formula’s that are used to aggregate the data should be able to handle a list. The underlying data is always provided as a list for aggregations. In the example the formula List.Sum is used to sum the sales numbers.

Please focus on step 3 for a moment. This argument provides a crucial hint for what formulas you can use to summarize text values. The aggregation formula needs to be able to handle a list as variable. When searching the Power Query formula reference you may stumble upon the formula Text.Combine. A formula that both combines text and takes a list as input. The formula returns a text value that is the result of joining all underlying text values. And each value that is concatenated, can be separated using a separator.

As input arguments, the formula requires text to concatenate (as a list) and it needs to know with what separator to use. This is exactly what we need for our challenge.

Manually Adjusting M-Code

You now know what formula to use for aggregating text. It’s time to adjust the M-code generated by the user interface. We can try to incorporate the Text.Combine function in the formula containing the error. The part of the formula returning an error is:

{"Seller", each List.Sum( [Seller] ), type text}

To have it use the Text.Combine function you can change it to:

{"Seller", each Text.Combine( [Seller] , ", "), type text}

The first argument indicates which column (received as a list) provides the text to combine. In this case the column ‘[Seller]’. The second argument tells us what separator to add between the text. The example contains a comma and a space (“, “). After pressing Enter, below screenshot is the result!

text.combine function to aggregate text values

The names of the Sellers are now all within the same cell, and don’t return an error anymore. Goal reached! That’s how easy adjusting M-Code can be. You’ll get more comfortable with writing code the more you do it.

To look up close, you can download the example file:

In this post you have learned that grouping data causes the underlying data to be provided to aggregation functions as a list. Knowing this, you can leverage functions that take a list as argument, when grouping your data. I hope this post opens your mind for more experimentation. If you have any questions, or stumble upon useful formulas to leverage when grouping, please leave a comment below.

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

33 thoughts on “Aggregate Text Values Using Group By in Power Query”

  1. Hi Rick,

    I was wondering if this little trick could also work inside the Table.AggregateTableColumn
    function. In principle it worked, however I failed in adding the separator argument, since the aggregations seem to either work differently or pass the arguments in a slightly different way?
    Do you have any idea?

    This works:
    Table.AggregateTableColumn(#”Merged Queries”, “My_Lookup_Table”, {{“Packagecode”, Text.Combine, “Combined Packagecodescodes”}})

    This doesn’t (also adding the ‘each’ statement doesn’t work):
    Table.AggregateTableColumn(#”Merged Queries”, “My_Lookup_Table”, {{“Packagecode”, Text.Combine([Packagecode], “, “), “Combined Packagecodescodes”}})

    • Hi Marc,

      That’s an interesting challenge. I didn’t try this before you asked. So your code is working like:

      Table.AggregateTableColumn(#”Merged Queries”, “My_Lookup_Table”, {{“Packagecode”, Text.Combine, “Combined Packagecodescodes”}})

      To make it working with a seperator you can do:

      Table.AggregateTableColumn(#”Merged Queries”, “My_Lookup_Table”, {{“Packagecode”, each Text.Combine( _ ", " ), “Combined Packagecodescodes”}})

      You were on the right path using the each keyword. You can then reference the “_” (underscore), to indicate it’s for each value within that column, and then add the separator as argument.

      Hope that helps and thanks for asking your question.

      Best regards,

      • Hi Rick,

        that was the missing link! Thank you so much, it works as expected and saved me quite some workarounds… AWESOME! Thanks 🙂

        kind regards,

  2. Thanks! Now can you help doing the same with a date column? I know somewhere you need to do a Date.ToText but it’s not working.

    • Hi H,

      Great question, that’s a little more difficult. What you can do is:

      {“Seller”, each Text.Combine( List.Transform( _ , (DateList) => Date.ToText( DateList ) ) , ", " ), type text }

      The part with List.Transform is added, so that Power Query can first transform each Date that needs to be grouped. Only to then combine them together.

      Hope this helps!

  3. Great! This helped me a lot.!
    What about if you just want to group and take the values for the latest one. Let me explain:
    In the example, let say you just want to now the values where the date is the oldest. Then you should have:
    19/04/2018 | Face Cream | Jaime | 90.07
    22/04/2018 | Hand Soap | Lisa | 74.05

  4. Thanks Rick, this helpled me a lot! A quick question: is there a quick way to remove potential duplicate values in the newly created column? Many thanks!

    • Hi Anne-Sophie,

      That’s a great question. This is the answer I gave to someone else in the comments:

      To get a list of UNIQUE seller names, you can add List.Distinct to the formula.

      The original formula for grouping in my example file is:

      = Table.Group(#”Changed Type”, {“Product”}, {{“Sales”, each List.Sum([Sales]), type number}, {“Sellers”, each Text.Combine([Seller], ” , “), type text}})

      You can change this to the following to get a unique list of names

      = Table.Group(#”Changed Type”, {“Product”}, {{“Sales”, each List.Sum([Sales]), type number}, {“Sellers”, each Text.Combine(List.Distinct([Seller]), ” , “), type text}})

      Hope that helps!

      • May I suggest including a Sort. Typically I will subsequently try to find Groupings that have similar text output.

  5. Can i accomplish this same csv text column result using #”Aggregate . . . ” given it in a left join scenario to produce the one left side hit for many right side hit results using #”Group By . . . ” requires defining every left side table column value you want included in the output results? I’ve outlined the scenario for this in more detail in this post .

  6. This is a great article, thank you so much for posting it! One other question, how do you update the new tables if new information is added to the original worksheet Source. I tried clicking on the ‘Refresh All’ in the Data Tab, but it didn’t add the new row of information from the Source Worksheet. Thank you!

    • Hi Stephanie,

      A ‘Refresh All’ should normally do the trick. In what form is your source data? Is it in a table and does Power Query pick up the table? If it’s not in a table, it could be that Power Query picks up the named range. Then if this named range is not including the additional data, it won’t be picked up.

      Best regards,

    • Hi Rachel.

      The grouping is required. But what you could do to keep all your columns, is to specify an additional column in the grouping screen. And in this extra column, select the operation : All Rows. After grouping, you will then see an additional column containing all the columns that were grouped earlier.

  7. Hi
    Just wanted to say this is the first article I read on this website and I can tell it has some really good content with great examples.
    On a side note: just bookmarked this site right next to Stackoverflow.

  8. Nice tip!!

    but how to make it a unique SELLER NAMES for eg!

    Sun Lotion 2951.06 Richard,Lisa,Dennis,Mark,Mark,James,James,James

    Req Result:

    Sun Lotion 2951.06 Richard,Lisa,Dennis,Mark,James

    • Hi Muhammed. That’s a great question! To get a list of UNIQUE seller names, you can add List.Distinct to the formula.

      The original formula for grouping in my example file is:

      = Table.Group(#”Changed Type”, {“Product”}, {{“Sales”, each List.Sum([Sales]), type number}, {“Sellers”, each Text.Combine([Seller], ” , “), type text}})

      You can change this to the following to get a unique list of names

      = Table.Group(#”Changed Type”, {“Product”}, {{“Sales”, each List.Sum([Sales]), type number}, {“Sellers”, each Text.Combine(List.Distinct([Seller]), ” , “), type text}})


Leave a comment