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.

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.

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.

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.

• powerbi
• powerbi-desktop

1 Answer 1

It's quite easy.

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

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.