TL;DR
Power BI’s Group By feature can combine multiple text values into a single row, useful when one entity, like a store, has several category tags that need to appear together. Switching the default Sum aggregation to Text.Combine in the Advanced Editor, adding a separator, and applying a conditional column afterward gives you one final, clean category value per row, ready for reporting or slicers.
Hi there, This Blog is about a scenario where you want to group the text data in one line. Let’s get started to understand it better.
Understanding the Scenario
Below is the Table with 2 columns – StoreName and StoreType.
Observation
One StoreName has Multiple StoreType. There are 4 Distinct Values of StoreType – Recreational, Medical, Hybrid, Any/Other.
Objective
One StoreName should have one StoreType only.
How to Group Text Values Using Group By in Power BI
Step 1: Apply Group By with Sum Aggregation
Click on the Group by Button present in the transform ribbon. Apply a sum aggregation on StoreType with StoreName as a basic selection. Let your new column name be – Text in One Line. With this, we will be able to make out what is the Unique Store Type status of distinct stores.
Step 2: Fix the Aggregation Error
Click OK and You will observe an error
Step 3: Edit the M Code in Advanced Editor
Click on Advance editor under Home Ribbon in your Query Editor.
By default, you will observe the following M Code:
As you can see in the last row inside the let code block List. Sum is mentioned. We need to change it to Text. Combine and Add a Separator of ‘-’ a hyphen for StoreType. Then, Click OK
You will observe the error will be removed and Your values in the Text in One Line Column will appear like below:
Multiple StoreType will be displayed next to distinct StoreName with a separator which will allow you to apply conditions as per your choice. Example
Any/Other-Hybrid = Hybrid
Any/Other-Medical = Medical
Any/Other-Recreational = Recreational
Hybrid-Medical = Hybrid
Hybrid-Recreational = Recreational
This will bring you close to your target objective.
Step 4: Add a Conditional Column for Final Categorization
Add a Conditional column or you can also create your logical conditions in Custom Column. Write your logical conditions and you will have one Distinct Value in FinalStoreType Column.
Hit Close & Apply and Save your Changes. Here we have achieved our required objective.
Conclusion
Text grouping in Power BI doesn’t need to be complicated once you know where the default Group By dialog falls short. By swapping the auto-generated Sum aggregation for Text.Combine in the Advanced Editor, you can turn multiple text values into a single, readable row, then use a conditional column to derive a clean, final category for reporting or slicers.
This same pattern applies well beyond the StoreType example here, anywhere you have one entity tied to multiple text values, whether that’s tags, statuses, or classifications, this Group By and Text.Combine combination is a fast, no-code way to consolidate your data without writing a single measure.
Frequently Asked Questions
Common questions about combining text values with Text.Combine and CONCATENATEX in Power BI.
Sum operation only works on numeric values, so applying it to a text column like StoreType triggers an error. Editing the M code in the Advanced Editor to use Text.Combine instead of Sum resolves this, since Text.Combine is built specifically for joining text values.
Text.Combine(texts as list, optional separator as text). For example, Text.Combine([StoreType], "-") joins all StoreType values for each group using a hyphen as the separator.
Text.Combine function, for example Text.Combine([StoreType], ", ") for a comma and space instead of a hyphen.
CONCATENATEX, for example CONCATENATEX(Table, Table[StoreType], ", "). This achieves a similar result to Text.Combine but works at the DAX/measure level rather than during data transformation in Power Query.
Text.Combine doesn’t exactly match the actual column name in your table, or when the value being combined isn’t already formatted as a list. Double-checking the exact syntax and column names in the Advanced Editor usually resolves it.
Text.Combine expressions for each column you want to consolidate into the grouped row.
Author By
Kamal Sharma
Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.
