Power BI - Grouping a table and including a column with dynamic comma separated values instead of splitting rows

 

Hey Guys, 

Recently I came across a requirement that needed a table with an unpivoted structure to be transformed in such away that it is grouped by a single column and the second column holds comma separated values of the corresponding column instead of it being split into rows (Example below).

Original Table

 

Resultant Table

I tried different approaches that included pivoting the table, replacing values with the required name, merging the columns, and replacing "0". It worked! But as all things are, its just not that simple. The requirement also required that additional batches could also be added to an existing fruit later. That meant it had to be dynamic.

The search continued. I stumbled upon a similar requirement on the Power BI forum that suggested using the Table.Group along with Table.Combine functions using M Query. I incorporated it to the solution, and it worked like magic. 

I followed the steps below on Power Query Editor,

• Group table by Fruit -> Give a meaningful column name under New Column Name ->  Select “All Rows” under Operations

The table will look like this (Grouped Fruit Column and Column Batches with a record of type Table)

• .      Open Advanced Query Editor available on the Home Tab or View Tab

(Home Tab)

(View Tab)

•  Modify the corresponding Grouped Rows step by adding Text.Combine function. For this example it’ll look like this,

Replace the section highlighted above with the section highlighted below 

(You can add a delimiter of your choice. I’ve added a comma).

And voila! We have the desired result, and its dynamic!

Hope this comes in handy!

Cheers!