How to transform grouped data?

alex-JF — Wed, 27 Dec 2023 07:12:37 GMT

Hi there, kinda difficult to explain what I want to get, therefore difficult to find something similar in forum, so I hope you can point me to the right stuff. To explain my point I'll prowide examples. Thank you in advance

Want to get:

john - mary,helga

bob - kate,alexa

smith - linda

Example source:

manager	customer
john	mary
bob	kate
bob	alexa
john	helga
smith	linda

Re: How to transform grouped data?

123abc — Wed, 27 Dec 2023 07:33:26 GMT

To transform the grouped data in Power BI from the example source to the desired format, you can use the following steps:

Load Your Data: Make sure you have loaded your data into Power BI. For this example, you'll have a table with columns named manager and customer.
Group Data:
- Go to the Home tab in Power BI.
- Click on Transform data to open the Power Query Editor.
- In the Power Query Editor:
  - Select the manager column.
  - Click on the Group By option in the ribbon.
  - In the dialog that appears, choose All Rows for the Operation and customer for the New column name. This will group the customers for each manager.
  - Click OK.
Expand the Grouped Column:
- After grouping, you will see a new column added, which contains lists of customers for each manager.
- Click on the small double-arrow icon in the column header of the new column to expand the lists.
- Choose to expand the lists into new rows.
Clean Up Data:
- You might end up with null values for some rows (like for the smith - linda row since there's only one customer). You can handle this by replacing null values with appropriate placeholders or by removing unnecessary columns/rows.
Rename and Format:
- Rename columns as needed. For instance, the grouped manager column might be renamed to just Manager, and the expanded customer column might be renamed to Customers.
- You can further format or manipulate the data as required for your visualization or analysis.
Close & Apply:
- Once you're done transforming the data in the Power Query Editor, click on Close & Apply in the top-left corner to apply the changes and return to the main Power BI window.

After these steps, your data should be transformed to the desired format:

DEX

john - mary,helga
bob - kate,alexa
smith - linda

Now you can use this transformed data in your Power BI reports and visualizations as required.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: How to transform grouped data?

alex-JF — Wed, 27 Dec 2023 09:37:42 GMT

I'm running into a problem:

when I try to expand

I'm ending up with my previous table without summarisation customers

what am I doing wrong? So when I'm trying to expand, I get new rows instead of new columns

Re: How to transform grouped data?

Fowmy — Wed, 27 Dec 2023 10:21:59 GMT

alex-JF
Please check the attached file

Re: How to transform grouped data?

alex-JF — Wed, 27 Dec 2023 11:08:11 GMT

many thanks it works!

topic Re: How to transform grouped data? in DAX Commands and Tips

How to transform grouped data?

Re: How to transform grouped data?

Re: How to transform grouped data?

Re: How to transform grouped data?

Re: How to transform grouped data?