Chris Webb's BI Blog

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

Pivoting Data In Power Query

with 11 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

11 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

  4. 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?

    zacksg1

    September 30, 2014 at 10:03 pm

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

      Chris Webb

      October 9, 2014 at 12:42 pm

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

    shaun

    February 24, 2015 at 9:56 pm

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

      Chris Webb

      February 24, 2015 at 10:15 pm

      • 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

        shaun

        February 24, 2015 at 10:24 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,733 other followers

%d bloggers like this: