How to Pivot Data with Duplicate Columns and Duplicate Index Columns in Power Query

twxmargerate
Jul 23, 2024
2 min read

Transforming table on the left to table on the right.

Let's look at the blue table below first, it can be seen that the Date column (to be pivoted) has duplicates while the Store ID (index column) has duplicates as well.

Note: Index column is the columns we want to group by and will remain unchanged during the pivot.

Here’s the final result that is desired where the Store ID (index column) remain to have the duplicates, while the dates became the column headers, and the data has all the employee names who work in each store on each day.

The problem here is if we straight away pivot the Date column in Power Query, the error like below would occur. This is because the employee names can't be aggregate and group by the index column/Store ID.

Here's how I solve this problem:

Import data into Power Query

First of all, definitely import the data into Power Query just like below.

Group Data Using Delimiter

Next, group the Names by the Data and Store ID through delimiter.

#"Grouped Rows" = Table.Group(#"Changed Type", {"Store ID", "Date"}, {{"Name", each Text.Combine([Name],";"), type nullable text}}),

Just key in the above M code into Power Query advanced editor. Remember to replace "Store ID", "Date" and "Name" to fit your condition.

Now the Names are grouped together in a row with the delimiter. Basically the Date and Store ID together become unique combination.

Split the Data by Delimiter

Then split the Name column through the delimiter.

At the Power Query Editor: Select the Name column > Go to Home > select Split Column > select By Delimiter.

Change the delimiter if you're using other delimiter then click on OK.

The Names will then be split just like the image below, you can remove the "Changed Type1" step if you want.

Unpivot the Table

Next, unpivot the table with Store ID and Date being static.

Select both Store ID and Date columns then right click and select "Unpivot Other Columns".

Here's the result of unpivoting the table. We're doing these steps to create a unique combination so that the table can pivoted. Now the Store ID, Date and the Attribute(resulted column of unpivoting the name) have unique combination together.

Pivot the Table

Now we're ready to pivot this table. We want to pivot the table so that each dates can be the header of each column.

Select on Date column > Go to Transform tab in the top panel > Select Pivot Column

We want our data in each to be the Name of the employees, the employee names is now in "Value" column, so choose the Value column, expand the Advanced options section and select Don't Aggregate because the we want each name to appear. Then click on OK.