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:
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.
The Pivot dialog box asks you to choose the Value column, which is Budget Amount in this example
and then simply and easily I have the pivoted result set;
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;
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,
So the default Pivot will result as below:
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
Here is the error value in the result set;
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;
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);
and then unpivoted result set would be as below:
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;
Then you would get that repeated in the attribute field after unpivot;
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 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.
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
Note that both people are located in the same country
Yes, as I mentioned is possible. just choose an aggregation
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.
Can I unpivot more than 1 column in one time?
Hi Michelle. that will be transformed to columns and cells.
What do you mean by un-pivoting more than one column at a time? Can you give me an example? normally there is one
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
Awesome introductory article. I am trying this out now.
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”
Hi Anton
It is hard to investigate the issue without seeing the data. Can you share your dataset with me?
Cheers
Reza
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.
you can create a custom function and run it for all those files. here is an example.
Cheers
Reza
Thanks