[SOLVED] Is there a way in Power BI Desktop to convert text columns into a single row with values grouped by another column?

Is there a way in Power BI Desktop to convert text columns into a single row with values grouped by another column?

I have a data set with two columns. One is a the name of a group and the second is the users in that group.

Group	User
Group A	User A
Group A	User B
Group A	User C
Group A	User D
Group B	User A
Group B	User E

I am trying to convert this in power bi desktop so that instead of a row for every user, I have a row for each group, with the first column being the group and the second column being a single string with all the users in it separated by a delimiter.

Group	User
Group A	User A; User B; User C; User D
Group B	User A; User B

I have tried using the various pivot and transpose options in power bi desktop but I am relatively new to using this and am not getting the results I would like and unsure if I am using them correctly.

Solution

It's quite easy.

So let's assume you have your table with the user data:

Group	User
Group A	User A
Group A	User B

Click on "Transform Data" to open the Power Query editor:

Now we have to group the data.

Select the Group column.
Go to the Transform tab and click Group By

In the Group By window:

Set Group as the column to group by.
In the New Column Name, type Users. Change operation to All Rows

Now your table should look like this:

Now go to the ribbon Add Column and add a Custom Column with this formula:

Text.Combine([Users][User], "; ")

After that your table should look like this:

Then you only have to delete the column Users and you're good to go.