Unpivot Columns in Power BI
Do you face these questions in Excel or Power BI or Power Query?
- How do I convert an Excel data set that is 133+ rows x 55+ columns into a proper data set for analysis?
- What is Unpivot Columns?
- What is Unpivot Other Columns?
Let’s understand the Problem that we face with our Data:
Consider the structure of the following two data sets:
Important: The first table is a Crosstab. It is not a Pivot Table friendly structure. The second one is.
Situation: You get multiple columns table. You need to change this into tables having separate Columns for Name and Value. The Unpivot Columns feature turns multiple column headers into a single column but in rows. The values get stored under the original columns in another column.
This is a simple example of Power BI Unpivot Multiple Columns.
Power BI Unpivot Columns feature converts the data headers stored horizontally into a vertical format i.e. Power Query Column to Rows. The chosen columns are then split in two columns i.e. Attribute and Value.
- Attribute represents the header of the columns chosen for Power BI Unpivot Columns
- Value represents the value which was present under the header’s column previously
In other words, data gets rotated from horizontal to vertical when Unpivot Columns feature is applied. The data set here gets re-structured in few seconds as compared to manual copy-pasting in Excel, which takes hours.
Power BI ‘Pivot Multiple Columns’ and Power BI ‘Unpivot Multiple Columns’ are the two Powerful Features of Power BI.
These are very helpful in:
- Converting your Data in crosstab format and flat file format
- Dealing with data analysis and Database Management Systems
In this post, we will learn about the basic steps on how to use Power BI Unpivot Columns feature to Unpivot columns to rows.
Then we will learn about the four Advance scenarios where you can use Unpivot Columns to convert column data fields to row data fields.
Content
Case 1: The Simple Unpivot
Consider that we have a data table as shown below.
It represents the response of the people for two questions.
We wish to transform the table structure, and Unpivot the data in tabular form.
Steps to Unpivot this Simple Data Table in Power Query Editor:
- To Unpivot in Power BI desktop, first you need to import the data in Power BI.
- For importing, first open Power BI desktop and click on the Home tab
- After that click on the Get Data This will show up the Dropdown list for selecting the Database sources
- Click on any of the data sources from which you want to extract the data. Here we are selecting the Excel option as shown in below image
- Load the data in Power Query Editor. To do so,
- Go to Home Tab
- Click on the Edit Queries option
- Load the data in Query Editor Window
- When your data table (Crosstab) is opened in Query Editor, sometimes the Header of the table shows Column1, Column2, Column3 and so on.
- To get back the original column name, carry out the following steps-
- First, go to the Transform tab in the Power Query Editor
- Click on the ‘Use First Row as Headers’ option
- It contains two options in the dropdown list. Click on the first option i.e. ‘Use First Row as Headers’. This will promote the row as the headers and thus, the original column header’s name will be restored automatically
- To get back the original column name, carry out the following steps-
- After restoring the original column’s header name correctly, select the columns that you wish to Unpivot. In our case, we want the two questions to appear vertically in multiple rows beside the names of the people. Hence, we need to select two columns representing these questions
- First, select the columns to Unpivot the data. For selecting columns, click on Header of the starting column
- Now, go to the Transform tab in the ribbon and locate the Unpivot Columns
- Click on the drop-down arrow and select either Unpivot Columns or Unpivot Only Selected Columns option as shown below
- Here we are selecting Unpivot Only Selected Columns option to Unpivot the selected data
Note: We have not selected the name column (first column) in this Unpivot Columns example here. It was already vertically placed.
- So here, the header of the columns has changed. The Headers change into Attribute (representing the original horizontal headers) and Value. You can change the header’s name as per your need. In this case, we are changing the header of the columns as Statement and Response respectively.
- To change the column name, right click on the column’s header to select the Rename option. Rename option will let you edit the column name or double click on the column’s header for changing the column name. After typing the column name, press Enter.
- If you have more rows added to the original data, you don’t have to repeat this entire process. Instead of recreating the query from scratch, use the Refresh Data option.
- To use Refresh button, follow the steps as shown below
- Go to the Home tab in Power Query Editor
- Click on the Refresh Preview This will update your data in Query Editor as shown in below image
- To use Refresh button, follow the steps as shown below
- Check for the created steps in the Applied Steps in Query Settings.
To go back to the previous step, we need to delete the current step. Click on the Delete Option present in front of the Applied Steps. It works like Undo (Ctrl + Z) feature.
Case 2: Unpivot Multi-Column Data
Consider that we have a data table as shown below.
The data is a crosstab.
The column headers represent two properties i.e. year ad data type (e.g. salary, incentives). In other words, it’s a combined header.
Let’s see how to deal with Power BI Unpivot Multiple Columns.
Steps to Unpivot the Multi-Column Data:
Here we want to Unpivot two columns separately.
- As explained in the Case 1, first import the data in Power BI.
- Go to the Home tab in Power Query Editor Window
- Then click on the Get Data This will show up the Dropdown list for selecting the Database sources
- Click on any of the data sources from which you want to extract the data. Here we are selecting the Excel option as shown in below image
- To load the data in Power Query Editor-
- Go to Home Tab
- Click on the Edit Queries option
- Load the data in Query Editor Window
- Now, select the columns where you wish to apply Unpivot. Here, we are selecting the below Columns–
- 2016Salary p.a
- 2017Salary p.a
- 2016Incentives p.a
- 2017Incentives p.a
- All these columns contain the values. Therefore, we are applying Unpivot only on these columns. To apply Unpivot, do the following-
- Select the columns to Unpivot multi-column data
- Click on the Transform tab in Power Query Editor
- Go to the dropdown list of Unpivot Columns
- Click on the Unpivot Only Selected Columns
- Now you need to Split the columns containing combined values. Here Attribute column contains combined values. Therefore select the Attribute column and follow the below steps for splitting-
- Go to the Home tab in the ribbon
- Select the Split Column option here
- Click on dropdown button to select the option ‘By Number of Characters’. This is because the prefix years represent 4 digits and remains consistent in the Attribute column. If there was a hyphen between the year and the salary/incentives, we will need to select the By Delimiter
- A new window opens after clicking on the By Number of Characters Specify the number of characters in the text b1ox here. This will split the columns. For e.g. split after first 4 characters i.e. year (2016, 2017 etc.)
- In the newly opened window “Split Column by Number of Characters” you will find three options. These Options specify the frequency and direction for splitting (from left / right). Click on the first option – Once, as Far Left as Possible and later
- Click on OK
- Here you will get to see the two Attribute columns, Attribute1 (year) and Attribute2 (payment type). Now you need to Pivot the columns in your Data table. Try out the below steps for applying Pivot-
- Select the column Attribute2 to Pivot the Multi-Column Data
- Go to the Transform tab in the Power Query Editor window
- Click on the Pivot Columns option
- A Power BI ‘Pivot Column Window’ will get open. Select the Value column from the Dropdown list
- Click on the Advanced Options to select the Value Function in the Dropdown list as listed below
- Count (All)
- Count (Not Blank)
- Minimum
- Maximum
- Median
- Average
- Sum
- Don’t Aggregate
Select the Option Sum in the Dropdown list to aggregate the numeric data.
- Click on OK
- After Pivoting the data, double click and rename the column headers as required. Here, we are Renaming the column Attribute1 as Year.You can also rename the column header by right clicking on the Attribute1 column and selecting the Rename option. This will let you to change the selected Column Header.Now, go to the Home tab and click on Close and Apply button to save the data.
In this way, you can apply Power BI Unpivot Columns to Multi-Column data types easily.The above images show you the step by step process to convert Multi-Column data types in Microsoft Power BI Unpivot data.
Case 3: Unpivot Columns with Dual-Row header
Consider that we have a data table as shown below.
The data is a crosstab.
The column has two headers i.e. master header (year) and sub-header (payment type).
In this case, we are going to learn how to use Power BI Unpivot Columns when the column headers are split over two rows.
Steps to Unpivot Columns with dual-row header:
- To Unpivot in Power BI desktop, first you need to import the data in Power BI
- For importing, first open Power BI desktop and click on the Home tab
- After that click on the Get Data This will show up the Dropdown list for selecting the Database sources
- Click on any of the data sources from which you want to extract the data. Here we are selecting the Excel option as shown in below image
- Load the data in Power BI Query Editor. To do so,
- Go to Home Tab
- Click on the Edit Queries option
- Load the data in Query Editor Window
- Now Transpose your entire Data table into rows. To Transpose the data-
- Go to the Transform Tab in the Power Query Editor
- Click on the Transpose Button. Your data will automatically get flipped
- There are some null values present in your data table. To remove the null values, you need to fill the data in your data table. This is done by using Fill Down option
- Select the Column1 and go to the Transform
- Click on the dropdown of Fill Down
- This will show Down and Up options in the Dropdown list. Select the Down option. This will repeat the value on each blank row below
- Select Column1 and Column2 and click on the Merge Column option. To do so,
- Select Column1 and Column2 for Merging
- Go to the Transform
- Click on Merge Columns
- When you click on the Merge Column option, a new window of Merge Columns will pop up. In this window, click on the dropdown list to get an option to add a separator between the values of Column1 and Column2
- Now, choose the Space option to add space between two values
- After choosing the Separator, Click OK
- Use the Transpose button to rotate all the columns from the Data table back to its original form. As discussed above-
- Go to the Transform
- Click on Transpose button to rotate the data table
- After Transposing, the first row of all the Columns contain the data that we need to place in Column headers.This can be easily done by using the Use First Row as Headers Option.
- Go to the Transform tab
- Click on the ‘Use First Row as Headers’ option.
- It contains two options in the dropdown list. Click on the first option i.e. ‘Use First Row as Headers’. This will promote the row as the headers and thus, the original column header’s name will be restored automatically
- You will get the data as discussed in Case 2. Repeat all the steps that you have learned in Case2.The image given below shows the steps to get the final result.
- Select the columns to Unpivot Dual-Row Header Data
- Go to the Transform tab
- Click on the dropdown list of Unpivot Columns option
- Select Unpivot Only Selected Columns option
- Split Attribute by Number of Characters
- Go to the Home tab in the ribbon
- Select the Split Column option here
- Click on drop down button to select the option ‘By Number of Characters’
- A new window opens after clicking on the By Number of Characters option. Specify the number of characters in the Text Box here. This will split the columns
- In the newly opened window “Split Column by Number of Characters” you will find three options. Click on the first option – Once, as Far Left as Possible and later.
- Click on OK
- Pivot Attribute2 and Value Columns
- Select the columns to Pivot the Dual-Row Header Data
- Click on the Transform tab
- Select the Pivot Column option
- Select the Value column from the dropdown list in Pivot Column window
- Select the Sum function into the Aggregate Value Function
- Click on OK
- Rename Columns
- After Pivoting the data, double click and rename the column headers as required. Here, we are Renaming the column Attribute1 as Year.You can also rename the column header by right clicking on the Attribute1 column and selecting the Rename option. This will let you to change the selected Column Header.Now, go to the Home tab and click on Close and Apply button to save the data.
This is how you Convert the Dual Row Headers into normal Column Headers and Unpivot the data effortlessly.
- After Pivoting the data, double click and rename the column headers as required. Here, we are Renaming the column Attribute1 as Year.You can also rename the column header by right clicking on the Attribute1 column and selecting the Rename option. This will let you to change the selected Column Header.Now, go to the Home tab and click on Close and Apply button to save the data.
Case 4: Pivot and Unpivot to Clean Up Multi-Row Header
Now, let’s consider that we have a data table as shown below.
The data is a crosstab.
Also, it has two headers i.e. master header (Date) and sub-header (Payment Category).
The Payment Category here, consists of two separate headers i.e Salary and Incentives.
In this case, we are going to learn about ‘how to use Power BI Unpivot Columns when the column headers are split over multiple rows and columns’.
Steps to Unpivot the Multi-Row Header:
- Import the Data in Power BI-
- For importing, first open Power BI desktop and click on the Home tab
- After that click on the Get Data This will show up the Dropdown list for selecting the Database sources
- Click on any of the data sources from which you want to extract the data. Here we are selecting the Excel option as shown in below image
- Load the data in Power Query Editor. To do so,
- Go to Home Tab
- Click on the Edit Queries option
- Load the data in Query Editor Window
- As you can see, the imported data is quite messy and untidy i.e. Row1= Date, Row2= Payment Category, Row3= Name. Transpose the data as given below to make it into a proper order.
- Go to the Transform
- Click on Transpose
Note: No need to select particular columns or rows before doing Transpose. This will swap all the rows and columns in the Data table.
- Fill Down the data to remove null values
- Fill the data by right clicking on the Header of Column1 to select the Fill Down Option. This will replace all the Null Values with the Values in Column1
- Click on the Down option to Fill Down the data. Similarly, use the Fill Down Option for all the columns wherever null values are present. You can also use this whenever you want the above values to be repeated below
- Right Click on Column3 and click on the Remove Option. This will remove the Column3 which contains the Null values
- Use the Fill Down function again for Column4 and Column5
- Select column4 and column5 and go to the Transform tab
- Select the Fill Down dropdown option
- Click on Down option from Fill Down button
- As you can see, the first row doesn’t hold any proper value. Therefore it can be removed. To remove the unwanted row-
- Go to the Home
- Click on the Remove Rows
- In the Dropdown list of Remove Rows option, click on the Remove Top Rows When you select the Remove Top Rows Option, a new window gets opened. In this new window, insert the numerical values so that you can delete or remove that many numbers of rows from the top of the Data table. Here, we are giving 1 as our input so that our first row which contains Null values can be removed.
- The table here looks good. We will now restore the Column header by clicking on the Use First Row as Headers Option. This promotes the values from first row of each column to the column headers.
- Go to the Transform
- Click on Use First Row as Headers Option
- Choose the option of Use First Row as Headers from the dropdown list
- Now, we are ready to apply Unpivot on the Data table
- Select the Value Columns (AbduSalaam and Jeffery) to Unpivot Multi-Row Header Data
- Go to the Transform tab
- Click on the dropdown list of Unpivot Columns
- Choose Unpivot Only Selected Columns This will Unpivot all the Columns containing these Values
- Now, Move the Attribute Column towards the left. To do this,
- Right click on Attribute column and select the Move option
- Click on the Left option from the Dropdown of the Move option
- Rename the column header as Attribute and Value as per your requirement. The image given below shows how you can clean up the Multi-Row Header using the Unpivot
- After Renaming Attribute Column, select the Category Column and apply the Pivot Column function
- Select the columns to Pivot the Multi-Row Header Data
- Click on Transform tab
- Click on Pivot Column option
- Select the value column
- Select the Sum option from Aggregate Value Function
- Click on OK
Here, you’ll get your final result.
Now, click on Close & Apply button after Pivoting.
This is how you can Unpivot the columns in Power BI easily and quickly even if the dataset is not in the proper format.
Conclusion:
In this Power BI Tutorial, you learned in detail about:
- Power BI Unpivot the Simple Data
- Power BI Unpivot Columns with Dual-Row header
- Power BI Unpivot multiple columns
- Power Query Unpivot multiple headers
- Unpivot Nested headings
- Power Query column to row
- Convert Column data fields to row data fields
- Power Query combine multiple rows