Pivot and Unpivot with Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2016-04-07_00h40_54

Turning columns to rows, or rows to columns is easy with Power Query and Power BI. There are many situations that you get a name, value data source, and wants to convert that into columns with values underneath. On the other hand many times you get multiple columns and want to change it to name, value structure with a column for name, another column for value. That’s why Pivot and Unpivot is for. In this post I’ll get you through basic pivot and unpivot. If you want to read more about Power Query read it from Power BI online book.

Pivot: Turning Name,Value Rows to Columns

Consider we have a data source like this:

2016-04-07_00h47_23

above data set is budget information. If we want to spread the table with a column for every month, we can simply use Pivot as below:

first click on the column that contains names, in this example it would be Month column. Then from Transform menu tab, choose Pivot Column.

2016-04-07_00h40_54

The Pivot dialog box asks you to choose the Value column, which is Budget Amount in this example

2016-04-07_00h42_59

and then simply and easily I have the pivoted result set;

2016-04-07_00h44_48

You can see that I have a column for each month now. Year column was just passed through. I can have as many as columns I want to pass through. The important factor for Pivot is that there should be a name column, and a value column. You can also see the Table.Pivot script of Power Query generated for this example in above screenshot.

Now let’s see what happens if name value is a bit different;

2016-04-07_00h50_18

In this example I have two records for a single name (Mth 11 2010 is repeated). and for some names I don’t have any records at all (Mth 4 2011 is missing)

Pivot dialog has the option to choose the aggregation function, and that is specially for cases that a name appeared more than once in the data set. default aggregation is Sum,

2016-04-07_00h53_42

So the default Pivot will result as below:

2016-04-07_00h54_11

However if the aggregation be set to Do Not Aggregate. then you will get an error when a name is repeated in the data set

2016-04-07_00h54_41

Here is the error value in the result set;

2016-04-07_00h55_11

and the error would be:

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
    List

So Pivot is easy and simple to do, but you have to be careful about the nature and quality of source data set. If it is normal to have a name repeated in the source data, then an aggregation needs to be set properly. if you expect each name to appear once, then setting it as Do Not Aggregate works better because you can use error handling mechanism in Power Query to handle error somehow.

Unpivot; Turning Columns to Rows; Name, Values

Unpivot does the reverse. it turns multiple column headers into a single column but in rows. and store their values in another column. Here is an example of Budget data that usually you get from finance department;

2016-04-07_01h04_00

You can click on Columns that you want to unpivot, and then select Unpivot columns (or you can do reverse, select pass through columns, and select unpivot other columns);

2016-04-07_01h04_27

and then unpivoted result set would be as below:

2016-04-07_01h05_01

As you see columns and their values are now converted to rows split in only two columns: attribute, and value.

If you get a repetitive column in the source data like below;

2016-04-07_01h06_24

Then you would get that repeated in the attribute field after unpivot;

2016-04-07_01h06_45

So the best way to handle that is to identify the repetitive column before applying unpviot. You can do these types of checking with Power Query scripts and other functions, If you want to read more about Power Query read it from Power BI online book.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

16 Comments

  • Hi,

    Is it possible to unpivot more than 1 column types? To use the example above, what if in addition to the Month1 to Month 12 columns, there was also Q1 to Q4 that you want to unpivot?

    I’m using power query and it doesn’t handle this scenario well. So instead of only having 12 rows for 2010, it has ( 12 * 4 = 48) rows.

    • Hi Tumi,
      If I understand your question correctly, unpivoting both month (12 unique values), and quarter (4 unique values) should end up with 48 (12*4) unique values. So what is wrong about it? why you expect to see 12? 12 will appear only if you unpivot by month.

      Cheers
      Reza

  • Is is possible to pivot even though a name is repeated in the dataset? For example:
    Column1 Column2
    Name David
    Title Manager
    Location USA
    Name Steve
    Title Analyst
    Location Denmark

    I am trying to use names in Column1 to create new columns. The Values Column would be Column2, but when I do not aggregate data, I get errors. Any thoughts?

      • Yes John, It is possible.
        You should choose an aggregation such as Max or Min. Aggregation will happen only on one value, so don’t worry about that. this is just for Pivot itself to understand that what to do when multiple rows happens, because it is combining multiple rows into one. normally one row has values and others are blank, so result of aggregation will be always one value.

    • Hi Michelle.
      What do you mean by un-pivoting more than one column at a time? Can you give me an example? normally there is one that will be transformed to columns and cells.
      Cheers
      Reza

  • I am working on live stream data, I want my value to act as row entries example i want column as different week and rows as different fields like total price,count of user etc but such scenario is not coming as unable to add that value field into rows/columns. Also the edit query option is not enabling on live stream data so cannot use that mode also to get the desire result. Please suggest a way to do it.

    • with live streaming data sets you cannot use Power Query yet. so you have to find a way to do it through questions of Q&A which might be a bit tricky but somehow possible.

  • Hi,

    I imported data from excel, unpivoted few columns and used the result to create multiple visuals.
    Now I want to use the original (pivoted) version of the dataset for creating a GANTT Chart.
    How can I do that?
    1. I want to have 2 copies – 1 pivoted and the other unpivoted.
    2. Even if I import the dataset again (in pivoted form) , will this automatically update/refresh the unpivoted dataset as well?
    3. Is it possible to create a duplicate of existing dataset without having to import data from excel again?

    Thank You,
    Parul,

    • Hi Parul,
      When you get data from the source, in Query Editor, before unpivoting it, you can right click on the query and get a REFERENCE from it.
      in that case your original query remains as is.
      then in your new referenced query you can do unpivot.
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">