Question

Is there a way in Power BI Desktop to transform multiple text column values into a single row, grouped by another column?
I have a dataset in Power BI Desktop where multiple text values appear across different rows for the same category. I need to consolidate these values into a single row while keeping them grouped by another column. What are the best Power Query or DAX techniques to achieve this transformation efficiently?

Answer 1

Yes, you can transform multiple text values into a single row grouped by another column using Power Query or DAX:

Power Query (Recommended Approach)

1. Load your data into Power Query.
2. Group by the desired column (e.g., Category).
   • Go to Transform → Group By
   • Select the grouping column (e.g., "Category").
   • Choose All Rows or aggregate using Text.Combine([Column], ", ").
3. Expand or modify as needed.

DAX Approach (Using CONCATENATEX)

Create a new measure or column:

CombinedText = CONCATENATEX( VALUES(Table[TextColumn]), Table[TextColumn], ", ")

This method works well but may not update dynamically in tables with slicers.