How to set up data in Power Query so that multiple items in one cell are uniquely chosen within a slicer

Question

[Moderator note: personal information removed] I have a data set in Excel with a column which has multiple employee names within the same cell (separated by a comma). When creating a PivotTable on the data I am trying to create a Slicer that will slice on any one individual employee's name (even if that row of data where their name appears is together with other employees). Thank you.

Answer 1

Hi @Jeremy Troy

My name is Jeovany, and I'd like to help you with your problem.

To illustrate the solution, I created a sample fake table, where multiple employees are assigned to the same Project Name/Index.

Once the table is uploaded to Power Query, please follow these steps:

1. Select all the columns of the table
2. Right Click >>Select Group By, Select Operation: All Rows
3. Then click OK

This will create a column with a table containing all the data columns by rows.

4. Then select the "Assigned Employees" and the previously added "Count" columns and remove the other columns.

5. Change the "Assigned Employees" column name (Optional)
6. Select the column >> Right Click >>Split Column>>By Delimiter

(in this case "comma & space)

This will, unfortunately, create more rows; however, it will keep the original table structure and also provide the individual employee name column to create the slicer you are after.

7. After expanding the "Count" column, the table will look like this

Exporting Back to Excel

1. Create the slicer from the Select Employee column
2. Then hide the column

The result

I hope this solution suits your scenario and goals.

Please let us know if you require further assistance.

Kind regards

Jeovany

Answer 2

Hi @Jeremy Troy,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, I kindly suggest you can try following this guidance:

Step 1: Use Power Query to split the names into rows

• Based on your information, I have set up a testing data as below

• Select all the data range > right click and select Get Data from Table/Range > make sure to click the box of "My table has headers" then click OK to open Power Query editor.
• In Power Query, select the Assigned User column > select Split Column > By Delimiter. In the new dialog window, you need to configure the options like I demonstrated below and click OK

• In case if some of your data have space before or after the comma, you can go to Transform > Format > Trim to trim the spaces to make the name clean

• Now select Close & Load in the top left to export the table back in Excel.

Step 2: Create Pivot Table

• Select all the transformed data > Insert > Insert PivotTable > choose the place you want to put the Pivot Table
• Put Assigned user in Rows, Amount as Values and add Property/WON if needed
• Now go to PivorTable Analyzer tab and choose Insert Slicer and select Assigned user

I hope the information provided proves useful. Please proceed with the outlined steps and let me know whether they resolve the issue. If not, I’ll be glad to continue working with you to find a solution. 

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out at any time. 

I look forward to hearing from you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have any extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 3

Hi,

In Power Query, simply got o Data > Split column and under Advanced Split data by rows.