One of the features I’ve loved right from the start in Power Query is the ability to UnPivot data right from the UI (I’m not alone – Ken Puls has written a good blog post summing up this functionality and is equally impressed). However earlier this year when I was trying to implement Life in Power Query, I found that I needed to do the opposite: I needed to pivot data. I was able to do this with some pretty complex M, but since then the nice people on the Power query team have added a new Table.Pivot() function to allow this to be done easily. Unfortunately pivoting is not something that can be done using the UI alone (at least not at the time of writing), but it’s pretty straightforward to write your own M expression to use this new function.
Let’s take the following table as a starting point:
You can see, in the Measure column, we have values Sales Units and Sales Value that tell us what the numbers in each row in the Value column represent. For most purposes, it’s better to pivot this data so that we have two columns, one for Sales Units and one for Sales Value. You can do this pivot operation in a single step using Table.Pivot(). Here’s an example script that loads the data from the table above and pivots it:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Pivot = Table.Pivot(Source, {"Sales Units", "Sales Value"}, "Measure", "Value")
in
Pivot
Here’s the output:
It’s worth pointing out that if you don’t include each distinct value from your chosen column in the second parameter, those rows will be lost after the pivot takes place. So
= Table.Pivot(Source, {"Sales Units"}, "Measure", "Value")
Returns just:
Listing out all the column names in that second parameter is a bit of a pain, so we can get a list with all the distinct values in that column and improve our expression as follows:
= Table.Pivot(Source, List.Distinct(Table.Column(Source, "Measure")), "Measure", "Value")
This returns the same result as our first query – it uses Table.Column() to get a list of all the values in the Measure column, then List.Distinct() to return only the distinct values from that list.
Finally, there is an optional fifth parameter that can be used to aggregate data. The following source table has two rows for Sales Value for March:
If I use my original expression the pivot will work but I’ll get an error value in the cell for Sales Value for March:
Specifying a function to aggregate the data in these scenarios, where two cells need to be pivoted to one, solves the problem:
= Table.Pivot(Source, {"Sales Units", "Sales Value"}, "Measure", "Value", List.Sum)
In this case I’m passing the function List.Sum() to get the value 10+15=25 in the cell for Sales Value for March.
UPDATE: this feature is now available through the UI. Still, it’s nice to know how to do it in code 🙂
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
More on the Power Query Language reference guide: http://office.microsoft.com/en-us/excel-help/learn-about-power-query-formulas-HA104003958.aspx
Chris, this is fantastic. Just had to do this myself, and this was a HUGE help!
Great blog! Do you know if it is possible to pivot on more than one column?
No, it isn’t possible using just Table.Pivot() – it can only pivot on column at a time. However it might be possible with a more complex expression, depending on what you want to do exactly.
The fact that you can choose what function to use for aggregation is super powerful. Is there a specific list somewhere of functions that can be used?
Not specifically for this, but you can see a full list of Power Query functions here:
http://office.microsoft.com/en-gb/excel-help/power-query-formula-categories-HA104122363.aspx
…and of course you can write your own functions too.
Thank you – you just saved my day
BTW in the latest builds of Power Query there’s now a pivot button in the Query Editor.
After a little experimentation, I realised there is a possible workaround to pivot on multiple columns by simply creating a new merged column from those you require and then pivoting on the merged column instead.
I large data file of raw samsung data. There are many columns. The columns names are in a seperate file in a list (so the column names load as rows in a separate query)
I want to data drive what goes in the {} is that possible? i.e. my column names for a raw dataset in a separate datafile. So the column names come in as rows, I want to pivot (transpose in old money) it round and union it with another data set so it forms the column headers. Maybe pivot isn’t the best way.
Am experimenting by comparing power query with an R script I have to see how doing the same task compares. In R I assign the column names to a vector and pass it to the column names property of the dataset.
Hi Shaun, yes this sounds very doable. Here’s what I think you should do:
1) Create a query that loads the data from your name file
2) Use the Transpose button in Power Query (it’s easier than Pivot, you’re right) so you get a table with one column for each column name
3) Create a second query that loads your raw data
4) Use the Append button to append the contents of the second query onto the end of the first
5) Click the Use First Rows As Headers button on the new query created by step (4) and you’ll have your raw data with the right column headers.
Yeah I found it. My version was out of date. It was recognising the pivot function but didn’t give what I expected. I updated the version to the latest preview and it works fine. Cheers
Hi Chris, I’m stuck on a pivot problem and can’t find solutions anywhere, but you seem to be the closest to the issue. The groundwork is that I have an online form that collects dates and hours (just your basic timesheet) plus other details…when the data connection pulls the form results into PQ, I end up with columns like this: NAME, DATE, HOURS, DATE2, HOURS2, DATE3, HOURS3, etc where date corresponds with the hours that have the same number to them.
Ideally, I’d like to have a consolidated list of NAME/DATE/HOURS, but that’s where I’m struggling. I’ve tried to unpivot all of the date/hours columns, then remove all the “numbers” from the resulting Attributes column, then re-pivot the Attribute/Value column, but I keep getting errors (enumeration errors, primarily) when I do that.
Any ideas if I should use a transpose or some intermediary step in order to get this to flow smoother?
Hi Jimmy,
I think you are getting errors since once you unpivot and clean up all of the entries in the attributes column to remove the numbers, then there are potentially lots of actual values associated with each (cleaned) attribute. You’ll either end up aggregating these values (which I don’t think you want to do) or getting an error due to multiple values essentially being placed in a single cell after the pivot.
One option might be to grab each set of same-numbered fields, change the names to eliminate the numbers, and append each set of these fields to the original Name/Date/Hours columns.
Thanks, Zack. I’ve thought about that as an option (and it appears that may be my only solution), but my concern is the amount of “additional” data columns that i will have to append and what that will do to the speed of the query itself. If I have columns labeled through 10, that means I’ll have to download (via the data connection) 10 different queries before appending – just seems it might get a little slow as data continues to come in.
Hi Jimmy,
You can actually do that all within PowerQuery, with only a single query to your source. Pull in all of the data from your source as you normally would. Then, delete all but the first set of columns. You can then open the advanced editor and see the syntax it uses for this step. It will essentially create a variable to represent the table you have just created. You can then copy and paste this line and edit it to delete all but the next set of columns you want to grab and assign this table to a new variable. You can then change the column names to eliminate the numbers. You can keep repeating this for all of the sets of columns you want to grab. Once each of these is stored in its own table variable, you can append all of those tables together.
Zack, I understand the theory behind what you’re saying, but I’m having issues with the nomenclature in M for this…Is it possible you could show me an example? I’m just not getting the translation from the Table.RemoveColumns line to storing the table as a variable and then referencing it in the append…any recommendations?
Hi Jimmy,
Here’s an example. Let me know if you have questions!
let
//Import the full table from wherever it is being imported from (I just pulled in a table from excel as an example)
Source = Excel.CurrentWorkbook(){[Name=”InputTable”]}[Content],
//Grab the first set of columns
ThingToAppend1 = Table.SelectColumns(Source,{“Name”, “Date”, “Hours”}),
//Grab the second set of columns (and rename the columns so they match the first set)
ColumnsGroup2 = Table.SelectColumns(Source,{“Name2”, “Date2”, “Hours2”}),
ThingToAppend2 = Table.RenameColumns(ColumnsGroup2,{{“Name2”, “Name”}, {“Date2”, “Date”}, {“Hours2”, “Hours”}}),
//Grab the third set of columns (and rename the columns so they match the first set)
ColumnsGroup3 = Table.SelectColumns(Source,{“Name3”, “Date3”, “Hours3”}),
ThingToAppend3 = Table.RenameColumns(ColumnsGroup3,{{“Name3”, “Name”}, {“Date3”, “Date”}, {“Hours3”, “Hours”}}),
//Append each set of columns to a single table
FinalResult = Table.Combine({ThingToAppend1,ThingToAppend2, ThingToAppend3})
in
FinalResult
Thanks, Zack! Worked like a charm and the data connection isn’t dealing with any performance issues from the original multiple query idea I had.
Hooray! Glad this worked for you!
And thank you from me for helping Jimmy!
Thanks from me too, Zack!
I’ve searched for solution of almost the same problem as Jimmy, and my solution was too complex:
1) Rename columns in source so they have same pattern (f.e., Data2, Hour2, Data3, Hour3 – 5 symbols with trailing digit)
2) UnPivoting these columns
3) Splitting result column “Attribute” so “Date” is separated from number and “Hour” also
4) Then Pivot column “Attribute.1” and removing column “Attribute.2”
But your solution is more clear and simple
Is there any option to use a table in the Power Pivot data model as a source for Power Query? Or optionally, to accomplish exactly what you did above with the “pivot” by querying Power Pivot in MDX to turn certain row fields into columns for output to an Excel worksheet table based on a Power Pivot table (the Power Pivot table has far too many rows,, but turning certain fields into columns would reduce the row size to a fraction of their current count and make the number manageable for an Excel worksheet). Thank you!
No, you can’t use a table in Power Pivot as the data source for a Power Query query (well, actually you can with a very nasty hack but I really don’t recommend it). However it is certainly possible to pivot data in MDX… but you can do pretty much anything you can do in MDX in a PivotTable. Why can’t you drag the fields you want to pivot onto the columns field of a PivotTable?
I need to have it provide non-numerical information where the values would be located. Basically the values are Approved or Failed (along with a description). So they won’t work in a traditional pivot table format. I also need it to be a table so users can modify a field with a yes/no drop box (to dispute the Approved or Failed categorization).
Did you know you can create DAX measures that return text values? See http://www.powerpivotpro.com/2012/01/mini-post-3-measures-can-return-text/
You can also see the results of an MDX query in a table rather than a PivotTable (see http://blog.crossjoin.co.uk/2009/12/18/binding-an-excel-table-to-the-results-of-an-mdx-query/ but there are other posts out there, and functionality varies depending on which version of Excel you’re using). You could write your own MDX query (http://blog.crossjoin.co.uk/2012/10/07/introduction-to-mdx-for-powerpivot-users-part-1/ and the rest of the series might help) against Power Pivot and then put the results into a table.
Thank you so much! Those links open up an entire realm of solution options! I have really only just begun getting into experimenting with Power BI tools, so this is unbelievably helpful.
I had been generating link back tables, but definitely need to look further into the mechanics of MDX. The text measures option really changes limitations I had incorrectly placed on the use of pivot tables.
I need to accomplish an almost identical output using an existing table in a Power Pivot data model but I understand you cannot access the data model with Power Query, and my Power Pivot data is too large to export as a table without reducing the row count. Is it possible to accomplish exactly what you did above with the “pivot” by querying Power Pivot using MDX to turn certain row fields into columns for output to an Excel worksheet table, using data in a Power Pivot table?
The table in the Power Pivot data model currently has too many rows to output to Excel and access with Power query, but turning certain fields into columns would reduce the row size to a fraction of their current count and make the number manageable for an Excel worksheet). Any recommendations on how to perform the same exact process you demonstrate above using MDX would be amazingly helpful, if possible. Thank you!
Chris
I have tried this in SSAS tabular however it does not work as expected or how it works in power bi.
The pivot statement works fine at creation – the transform columns are created, but it does not add new columns dynamically as the pivot statement does in power bi at reprocesses of the model .
Can you give any advice?
Hi Chris,
as Stefan Persson write I also have an issue with Table.Pivot function on SSAS tabular.
I use this function to pivot values to fields, In the initial process I had 10 values to pivot and now added 5 other field. I expected after the model refresh to see additional 5 columns but it’s not work.
to add those 5 additional columns I had to open manually PQ, refresh preview and load to tabular model.
Can you give any advice?
Great explanation. It is possible to pivot a table with more Value columns, say Value1 and Value2 ?