Pivot and Unpivot with Power BI

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.

Video

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

23 thoughts on “Pivot and Unpivot with Power BI

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

    • CORRECTION:
      Column1 Column2
      Name David
      Title Manager
      Location USA
      Name Steve
      Title Analyst
      Location USA

      • 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

  • Hello Reza, very good information.
    I have a question regarding Pivot colum, I have many columns, but no column with value. Is there a way that you still can Pivot a column?
    Regards, Johan

    • Hi Johan,
      You still want to show a value in the cell’s value of your table, that is your value column. this value column might not be a numeric column to aggregate. In that case you set the advanced option in Pivot to Do Not Aggregate.
      Cheers
      Reza

  • Great article! I am running into a strange scenario where I am receiving a “DataFormat.Error: We reached the end of the buffer” as soon as I use the Pivot function. I am trying to pivot one column “label” (name) and display the second column “value” (selected as value). The “label” column has 11 values repeating for each record i.e. it must create 11 columns and then add the correct “value” under it. It works fine when I have a small amount of rows/records (around 500) but the data I need to apply this to is around 6000 records. You will see in the code below that I am using a function to retrieve the data from the API using a range “{1..numberOfPages}} where the parameter “numberOfPages” = the total number of records/100 to establish the amount of pages. This is to deal with pagination. I don’t beleive the issue is here as I can pull all 6000 records if I remove the Pivot. The data is loading and displaying but as soon as I add the pivot I receive the error. Almost as if I am trying to add too many columns for the amount of rows (running out of memory).

    Sample M code:
    let
    numberOfPages = PagesTotal,
    Source = {1..numberOfPages},
    #”Converted to Table” = Table.Buffer(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
    #”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “page”}}),
    #”Added Custom” = Table.AddColumn(#”Renamed Columns”, “Custom”, each GetSTdata([page])),
    #”Expanded Custom” = Table.ExpandTableColumn(#”Added Custom”, “Custom”, {“Column1”}, {“Custom.Column1″}),
    #”Expanded Custom.Column1″ = Table.ExpandRecordColumn(#”Expanded Custom”, “Custom.Column1”, {“customer”, “customFieldValueJSon”}),
    #”Parsed JSON” = Table.TransformColumns(#”Expanded Custom.Column1″,{{“customFieldValueJSon”, Json.Document}}),
    #”Expanded customFieldValueJSon” = Table.ExpandListColumn(#”Parsed JSON”, “customFieldValueJSon”),
    #”Expanded customFieldValueJSon1″ = Table.ExpandRecordColumn(#”Expanded customFieldValueJSon”, “customFieldValueJSon”, {“label”, “value”}, {“label”, “value”}),
    #”Filtered Rows” = Table.SelectRows(#”Expanded customFieldValueJSon1″, each true),
    #”Replaced Value” = Table.ReplaceValue(#”Filtered Rows”,null,”Ignore”,Replacer.ReplaceValue,{“label”}),
    #”Pivoted Column” = Table.Pivot(#”Replaced Value”, List.Distinct(#”Replaced Value”[label]), “label”, “value”)
    in
    #”Pivoted Column”

  • I get 24 files in a month which I need to unpivot, and few has different columns so I cant Append them together. How do I Create 1 query and use it to run a batch on all the file independently.

    In case I append those queries. Will I get a descriptor column to segregate the data for future use.

Leave a Reply