Pivot Text Values in Power Query – Explained

The above video breaks down how you can pivot text values in Power Query. The regular pivoting of numbers is straightforward. But when text values are involved, things can get a bit more tricky. This post starts with an explanation of pivoting, then moves on to pivoting numbers, and then ends with the method to pivot text values.

Table of contents

• Pivot Column, what does it do?
• Pivot Number Values
• Pivot Text Values
  • The Challenge With Text Values
  • Using an Aggregation Function
  • Using an Index Column
• Conclusion

Pivot Column, what does it do?

So what does the Pivot Column operation in Power Query do?

Suppose you have a dataset like below. There is a column for Month, Amount and Region. All is presented in a so-called tabular format. You can imagine that both the Month and Region columns are in the rows, and the Amount values are in the Values Section of a pivot table.

When you pivot a column, just imagine moving a column from the rows section of a pivot table to the columns section. If we would do this for the month column, all the unique month values would show as separate columns, instead of values in a single column.

Pivot Number Values

We continue the above example. This section is called Pivot Column with Number Values because the Amount column with numbers is aggregated during the Pivoting. To pivot the Month column:

• select the Month column. You want to create a unique column name for each unique value in the month column
• Go to Transform -> click Pivot Column
• As Values Column make sure the selection states Amount
• Open the Advanced Options and notice the default Aggregate Value Function is set on Sum. You don’t need to change anything here, but it’s good to be aware of the selection.
• Press OK

After the Pivot Column operation, the data now looks as follows:

The unique month values now all have their own column. Since the Aggregate Value Function was set to Sum, it means that duplicate combinations of Month and Region are summed up.

Pivot Text Values

Another situation is where you want to use the Pivot Column operation to Pivot Text Values. A challenge arises here because text values can not be summed together.

The Challenge With Text Values

For the below dataset, you want to Pivot the Month values, so they all get their own column. And the values column, in this case, is the Name.

In the earlier example, the Aggregate Value Function was set to Sum. Since you can’t Sum text values, we will have to handle this differently. To start the pivoting:

• Select the Month column
• Go to Transform -> click Pivot Column
• As Values Column, make sure to select Name.
• Open the Advanced Options and notice the default Aggregate Value Function is set on Count (All). This operation would work just fine, but to get the desired result, change the selection to Don’t Aggregate.

The result is in the below table.

So far, the Pivot Column operation seems to work for text values. But there’s a catch. The previous operation only worked because we had an index column. The index column worked as an anchor for the Pivot Column operation. Let’s see what happens when we remove it.

The starting dataset would then be:

When we know:

• select the Month Column
• then select Name as Values Column
• Have Don’t Aggregate as Aggregate Value Function under advanced options
• Press OK

Below is the result:

As you can see, Power Query throws an error saying: “Expression.Error: There were too many elements in the enumeration to complete the operation.“

To be able to Pivot the text values, there are two main approaches you can take. We will handle both in the following section.

Using an Aggregation Function

As we learned in the previous chapter, the problem is that during the Pivot operation, multiple items are associated with each column. And Power Query tries to put multiple values into a single cell.

To solve this, let’s take a closer look at the function generated when we do the Pivot Column operation. In the last picture, you can see the Table.Pivot function is applied when pivoting columns.

= Table.Pivot(
    #"Changed Type", 
    List.Distinct(#"Changed Type"[Month]), 
    "Month", 
    "Name"
 )

The arguments for this function are:

• a table (the table to transform)
• pivotValues as a list (the unique values that will turn into columns)
• AttributeColumn as text (the column that needs to be pivoted)
• ValueColumn as Text (the value column for the Aggregate Value Function)
• Optionally: an aggregationFunction

The first four arguments are already provided in the above formula on lines 2-5. You will now make some minor adjustments by including the 5th argument: the Aggregation Function.

To combine the text values into a single cell, you can add the following code as 5th optional argument: each Text.Combine( _ , ” , “). Resulting in the following code:

= Table.Pivot(
    #"Changed Type", 
    List.Distinct(#"Changed Type"[Month]), 
    "Month", 
    "Name",
    each Text.Combine( _ , " , ")
 )

Notice how the text values are now nicely concatenated in the same cell.

Using an Index Column

Depending on your needs, an aggregation function can suffice. In other instances, you may want a separate line for each of the values. That’s what this approach does. We will start from the dataset below.

To have a separate line for each value in the month column, you first need to know how many items a month can have. To do that:

• Select the Name column -> click Group By in the home tab
• Add a new column with the All Rows operation and name the column Details
• Click OK

This adds a table object to each of the remaining rows. We can easily find out the amount of unique Name values by adding an Index column to the dataset.

• Go to the tab Add Column -> select Index Column -> From 1.
• Expand the arrows in the Details column, selecting only the Month Column and click OK.

You should now have your data prepared for the Pivot Column operation, and it looks like the below:

What’s left is:

• Select the Month column -> go to Transform -> click on Pivot
• Put Name as Values column
• Make sure that under Advanced Options Don’t Aggregate is selected.
• Click OK.

That’s how you pivot text values using an index column. The Index column behaved as an anchor for the Table.Pivot function. At this point, feel free to remove the Index column and end up with a clean dataset.

Conclusion

In this post, you learned different aspects of the Pivot Column operation. How it behaves differently for text compared to numbers. And how slight adjustments in the code, can change the behaviour of the Table.Pivot function.

Want to follow along with the file? You can find it here:

Does this help? I’d love to hear what challenges you face when Pivoting Columns. Let me know in the comments what your experience is so far.

And most of all, enjoy Power Query!