Power Query Group By: Concatenate Text by Group

Yes. Power Query’s Group By dialog does not offer a direct “concatenate text” operation, but you can group rows and combine each group’s text with the M function Text.Combine. For a text column named Comment, a typical aggregation is Text.Combine(List.RemoveNulls([Comment]), "; "), which joins the non-null comments with a semicolon and a space.

What Group By does in Power Query

Power Query’s Group By feature turns repeated values in one or more columns into groups and calculates a result for each group. For example, a sales table might contain many rows for each department. Grouping by Department can produce one row per department, with a total, a row count, or another summary.

Text concatenation is a summary too, but it is not one of the standard operations in the Group By dialog. The built-in choices typically cover calculations such as sum, count, minimum, maximum, and all rows. To combine text values, you provide a custom aggregation in the Power Query M language. This keeps the result refreshable: when the source data changes, refreshing the query recalculates each group’s combined text.

This is useful for creating a compact summary from comments, product names, tags, or other text stored across multiple records. It does not merge cells in the worksheet; it creates a new query result table with one output row for each group.

Concatenate text with Table.Group

For a repeatable solution, use Table.Group in the query’s M code. The example below assumes the source query is named Source and contains text columns named Department and Comment. It returns one row per department and joins that department’s comments with a semicolon and a space.

let
    Grouped = Table.Group(
        Source,
        {"Department"},
        {
            {
                "Combined comments",
                each Text.Combine(List.RemoveNulls([Comment]), "; "),
                type text
            }
        }
    )
in
    Grouped

In the aggregation, [Comment] refers to the Comment values in the current group, not the entire original column. List.RemoveNulls removes null entries from that group, and Text.Combine joins the remaining text using the delimiter provided as its second argument. Change the delimiter to a comma, a line break, or another separator to suit the report.

To use the code, open the query in Power Query Editor, choose Home > Advanced Editor, and adapt the relevant step. If the query already has a final step, apply Table.Group to that step rather than assuming the source step is literally called Source. The column names in the code must match the query’s column names, including spaces and punctuation. The text type declaration tells Power Query that the new output column contains text.

Build the result through the interface

You can also create the grouping visually and add the concatenation as a custom column. This approach is useful if you prefer to inspect the intermediate grouped data before writing the aggregation.

In Power Query Editor, select the column or columns that identify a group, such as Department.
Choose Home > Group By. In the dialog, choose Advanced if you need to group by multiple columns.
Choose All Rows as the operation and give the nested-table result a name, such as AllRows. Confirm the dialog. The query now has one row per group and a column containing a small table for each group.
Choose Add Column > Custom Column. Name the new column Combined comments and enter Text.Combine(List.RemoveNulls([AllRows][Comment]), "; ") as its formula.
Remove the AllRows column if you do not need the nested tables in the final output, then set the new column’s data type to Text if Power Query has not already done so.

The custom-column expression reads the Comment column from each nested table, removes nulls, and combines the resulting list. If your nested-table column or text column has a different name, replace AllRows or Comment accordingly. The Group By dialog’s All Rows operation is not itself the concatenation; it preserves each group’s records so the custom column can process them.

Handle blank values and non-text data

A null and an empty string are different values. List.RemoveNulls removes nulls, but it does not remove a value containing no characters or a string made only of spaces. As a result, blank entries may create extra separators. If you want to skip both nulls and whitespace-only entries, use this version when Comment is a text column:

Text.Combine(
    List.Select(
        List.RemoveNulls([Comment]),
        each Text.Trim(_) <> ""
    ),
    "; "
)

Text.Trim is used to test whether a value contains anything besides surrounding whitespace. The original value is kept in the output, so a comment with intentional leading or trailing spaces is not changed. If you want to clean the values as well, transform them with Text.Trim before combining.

If the source column contains numbers, dates, or a mixture of types, convert values to text deliberately before concatenating. For example, for a column that contains numbers as well as text, an aggregation can use Text.Combine(List.Transform(List.RemoveNulls([Value]), each Text.From(_)), ", "). Think about how dates and numbers should be displayed: their textual format can depend on culture or conversion settings. For dependable reporting, normalize the column to the intended text format before grouping.

Choose the group columns and separator carefully

The group key determines which rows end up in each combined string. Grouping only by Department combines every comment in a department. Grouping by both Department and Month produces a separate combined value for each department-month pair. In the Group By dialog, Advanced lets you specify multiple grouping columns; in M, list each key column in the second argument to Table.Group, such as {"Department", "Month"}.

The separator affects readability and what people can do with the result later. Common choices include:

", " for short labels or names that do not contain commas.
"; " when the text may itself contain commas.
" | " when a clearly visible boundary is useful.
A line break, written in M as #(lf), when the result will be read as multiple lines in a cell or another text field.

A separator does not make the combined value a structured list. If an individual comment contains the same separator, a later reader may not be able to tell which separator characters belong to the comment and which were inserted between values. If the output will be exported or split apart again, choose an escaping or structured-data approach rather than relying on an ambiguous delimiter.

Ordering, duplicates, and output size

Concatenation preserves values as a text string, but readers often expect the pieces to appear in a particular order. Do not assume that the final order of grouped rows is meaningful: grouping does not promise a particular ordering of the output groups. If the order of comments within each combined result matters, add a sequence or date column and design the aggregation to sort each group by that column before combining. A simple sort step before grouping may be sufficient for a given query, but for a strict requirement, sort the rows inside the group’s aggregation using the desired key.

Repeated source values remain repeated in the combined text. To remove duplicates, apply a distinct operation to the values in each group before calling Text.Combine. For example, Text.Combine(List.Distinct(List.RemoveNulls([Comment])), "; ") joins distinct non-null comments. Distinctness is based on the values Power Query sees; it does not automatically trim spaces or treat differently capitalized strings as identical. Clean or normalize the text first if those differences should not count.

One group can also produce a very long combined value. Large results can be awkward to read, may be restricted by the destination system, and can make a worksheet less useful than the source detail. If the text is lengthy, consider keeping the records in separate rows, producing a count or summary instead, or loading the detailed result to a data model or database rather than forcing everything into one cell.

Common errors and ways to fix them

“The field wasn’t found.” Check the spelling of the column name and the step used as the input to the grouping. A renamed column or a name containing spaces must be referenced exactly.
A type-conversion error appears. Confirm that the values are text, or convert non-null values with Text.From before combining. Check date and number formatting if the precise displayed text matters.
There are doubled separators or empty sections. The source may contain empty strings or whitespace rather than nulls. Filter those values out using the blank-handling pattern above.
The result contains duplicates. Use List.Distinct if duplicate values should appear only once; otherwise, duplicates correctly represent repeated source rows.
The values appear in an unexpected order. Sort within each group by a meaningful date or index before combining. Sorting the final grouped table only changes the order of groups, not the order of items inside each string.

Also check whether a previous query step has already filtered or transformed the rows. Group By operates on the table at the step where it is applied, so removed rows cannot appear in the concatenated result.

Power Query versus worksheet formulas

Power Query is a good fit when the result should be rebuilt from imported or regularly refreshed data. The grouping and concatenation steps become part of the query, and refresh applies them again. A worksheet formula may be more convenient for a small, directly edited range, but formulas can require extra setup to group records and may be less suitable for a repeatable import-cleanup process.

Excel’s TEXTJOIN function can join a selected range, but it does not by itself perform Power Query’s group-by transformation. In Microsoft 365, formulas can combine dynamic-array functions to build more involved results, but that is a different workflow. If the data arrives from files or a database and you need a dependable refresh process, keep the transformation in Power Query. If users need to edit individual comments directly in worksheet cells and see an immediate result, a formula-based design may be easier to maintain.

Frequently Asked Questions

Can I concatenate text directly in the Group By dialog?
No. The standard dialog does not include a concatenate operation. Choose All Rows and add a custom column, or edit the query with Table.Group and Text.Combine.

Does Text.Combine ignore null values?
Yes, Text.Combine ignores null values in the list it receives. Using List.RemoveNulls as well makes the handling explicit. Empty strings are not null, so filter them separately if they should be skipped.

How do I concatenate values with a comma?
Use a comma and a space as the separator: Text.Combine(List.RemoveNulls([Comment]), ", "). Change Comment to the name of the text column in your query.

How can I combine text by more than one column?
Include all the key columns in the Group By dialog’s Advanced settings. In Table.Group, list each key in the second argument, for example {"Department", "Month"}.

Can I remove duplicate text before combining?
Yes. Apply List.Distinct to the group’s list before Text.Combine. If leading spaces, trailing spaces, or capitalization differences should not create separate values, normalize the text before removing duplicates.

Will the combined text update when the source changes?
Yes, when the query is refreshed and its source is available. Power Query recalculates the grouped result from the rows included by the query’s preceding steps.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.