How to transform grouped data?

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

1. 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.

2. 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.

3. 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.

4. 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.

5. 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.

6. 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.