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