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