Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Pivoting Data In Power Query

with 4 comments

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:

image

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:

image

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:

 
image

 

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:

image

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:

image

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)

image

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.

Written by Chris Webb

November 25, 2013 at 9:30 am

Posted in Power Query

4 Responses

Subscribe to comments with RSS.

  1. Andrea Uggetti

    November 26, 2013 at 11:03 am

  2. Chris, this is fantastic. Just had to do this myself, and this was a HUGE help!

    Ken Puls

    January 17, 2014 at 8:10 pm

  3. Great blog! Do you know if it is possible to pivot on more than one column?

    Justin

    June 12, 2014 at 5:14 pm

    • 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.

      Chris Webb

      June 13, 2014 at 1:34 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: