Unpivot Columns in Power BI

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.
  1. For importing, first open Power BI desktop and click on the Home tab
  2. After that click on the Get Data This will show up the Dropdown list for selecting the Database sources
  3. 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,
  1. Go to Home Tab
  2. Click on the Edit Queries option
  3. 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-
    1. First, go to the Transform tab in the Power Query Editor
    2. Click on the ‘Use First Row as Headers’ option
    3. 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
       
• 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
  1. First, select the columns to Unpivot the data. For selecting columns, click on Header of the starting column
  2. Now, go to the Transform tab in the ribbon and locate the Unpivot Columns
  3. Click on the drop-down arrow and select either Unpivot Columns or Unpivot Only Selected Columns option as shown below
  4. 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
    1. Go to the Home tab in Power Query Editor
    2. Click on the Refresh Preview This will update your data in Query Editor as shown in below image
       
• 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 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
  1. For importing, first open Power BI desktop and click on the Home tab
  2. After that click on the Get Data This will show up the Dropdown list for selecting the Database sources
  3. 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

1. Load the data in Power BI Query Editor. To do so,
   1. Go to Home Tab
   2. Click on the Edit Queries option
   3. Load the data in Query Editor Window

• Now Transpose your entire Data table into rows. To Transpose the data-
  1. Go to the Transform Tab in the Power Query Editor
  2. 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
  1. Select the Column1 and go to the Transform
  2. Click on the dropdown of Fill Down
  3. 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,
  1. Select Column1 and Column2 for Merging
  2. Go to the Transform
  3. Click on Merge Columns
     
     
     
  4. 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
  5. Now, choose the Space option to add space between two values
  6. 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-
  1. Go to the Transform
  2. 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.
  1. Go to the Transform tab
  2. Click on the ‘Use First Row as Headers’ option.
  3. 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.
  1. Select the columns to Unpivot Dual-Row Header Data
  2. Go to the Transform tab
  3. Click on the dropdown list of Unpivot Columns option
  4. Select Unpivot Only Selected Columns option
     
• Split Attribute by Number of Characters
  1. Go to the Home tab in the ribbon
  2. Select the Split Column option here
  3. Click on drop down button to select the option ‘By Number of Characters’
     
  4. 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
  5. 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.
  6. Click on OK
• Pivot Attribute2 and Value Columns
  1. Select the columns to Pivot the Dual-Row Header Data
  2. Click on the Transform tab
  3. Select the Pivot Column option
     
  4. Select the Value column from the dropdown list in Pivot Column window
  5. Select the Sum function into the Aggregate Value Function
  6. 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.

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-
  1. For importing, first open Power BI desktop and click on the Home tab
  2. After that click on the Get Data This will show up the Dropdown list for selecting the Database sources
  3. 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,
  1. Go to Home Tab
  2. Click on the Edit Queries option
  3. 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.
  1. Go to the Transform
  2. 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
  1. 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
  2. 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
  1. Select column4 and column5 and go to the Transform tab
  2. Select the Fill Down dropdown option
  3. 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-
  1. Go to the Home
  2. Click on the Remove Rows
  3. 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.
  1. Go to the Transform
  2. Click on Use First Row as Headers Option
  3. Choose the option of Use First Row as Headers from the dropdown list
     
• Now, we are ready to apply Unpivot on the Data table
  1. Select the Value Columns (AbduSalaam and Jeffery) to Unpivot Multi-Row Header Data
  2. Go to the Transform tab
  3. Click on the dropdown list of Unpivot Columns
  4. 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,
  1. Right click on Attribute column and select the Move option
  2. 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
  1. Select the columns to Pivot the Multi-Row Header Data
  2. Click on Transform tab
  3. Click on Pivot Column option
     
  4. Select the value column
  5. Select the Sum option from Aggregate Value Function
  6. 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:

1. Power BI Unpivot the Simple Data
2. Power BI Unpivot Columns with Dual-Row header
3. Power BI Unpivot multiple columns
4. Power Query Unpivot multiple headers
5. Unpivot Nested headings
6. Power Query column to row
7. Convert Column data fields to row data fields
8. Power Query combine multiple rows