The above video breaks down how you can pivot text values in Power Query. The regular pivoting of numbers is straight forward. 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, to then end with pivoting text values.
Table of contents
Pivot Column, what does it do?
So what does the Pivot Column operation in Power Query do? Imagine having a dataset like below. There is a column for Month, Amount and Region. All is presented in a so called tabular format. You could imagine that both the Month and Region column 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 column, instead of values in a single column.
Pivot Column with Number Values
We continue 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 Column with Text Values
Another situation is where you want to use the Pivot Column operation together with a column with text. 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 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 now:
- 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.“
In this case, column 1, which is Month 1, has N1, N2, N3, N4 and N5 related to it. The reason for the error is that for each month column, Power Query tries to force all values into a single cell. As there is no aggregation function added, Power Query does not know how to handle this and kindly returns an expression error.
To be able to Pivot the text values there’s two main roads you can take. We will handle both in the following chapters.
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 function generated when we do the Pivot Column operation. In the last picture, you can see that 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 list (the unique values that will turn into columns)
- AttributeColumn as text (the column that needs to be pivot)
- 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 do some minor adjustments by including the 5th argument: the Aggregation Function.
If we want to make all values visible in the same cell, one way to go is to use the Text.Combine function. This function takes a list as its first argument, and an optional separator as its second argument. Here you can find all functions that result in a list.
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 below dataset.
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 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.
The result is that you Pivoted your 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.
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 behavior 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!