Unpivot Columns in Power BI

Unpivot Column in PowerBI Featured Image

Do you face these questions in Excel or Power BI or Power Query?

  1. How do I convert an Excel data set that is 133+ rows x 55+ columns into a proper data set for analysis?
  2. What is Unpivot Columns?
  3. 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:

Crosstab vs Unpivot Simple Data

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:

  1. Converting your Data in crosstab format and flat file format
  2. 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

Simple Data

Simple data has a simple table structure. It contains a single column header for each column. Let’s learn how to Unpivot this data.

Multi-Column Data

Multi-Column data consists of complex data structure. It has combined column header for each column.

Dual - Row Header

It is the data structure that has two column headers for each column. One is the master header and second is the sub-header.

Multi - Row Header

It is the data structure that has multiple column headers. The column headers are split over multiple rows and columns.

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.

Crosstab vs Unpivot Simple Data

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
      Import Data in Power BI
  • 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
      Load Data in Power BI
  • 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
        Change Column Header for Simple Data
  • 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.

Unpivot Columns for Simple data

Simple Data before vs after applying unpivot

  • 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.Rename column headers for simple data
  • 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
        Refresh simple data
  • 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.
    Delete steps for simple data

Simple Data

Simple data has a simple table structure. It contains a single column header for each column. Let’s learn how to Unpivot this data.

Multi-Column Data

Multi-Column data consists of complex data structure. It has combined column header for each column.

Dual - Row Header

It is the data structure that has two column headers for each column. One is the master header and second is the sub-header.

Multi - Row Header

It is the data structure that has multiple column headers. The column headers are split over multiple rows and columns.

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.
Crosstab vs Unpivot Multi Column Data

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.
    1. Go to the Home tab in Power Query Editor Window
    2. Then 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

Import Data in Power BI

  • To load the data in Power Query Editor-
    1. Go to Home Tab
    2. Click on the Edit Queries option
    3. Load the data in Query Editor Window

Load Data in Power BI

  • 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-
    1. Select the columns to Unpivot multi-column data
    2. Click on the Transform tab in Power Query Editor
    3. Go to the dropdown list of Unpivot Columns
    4. Click on the Unpivot Only Selected Columns

Unpivot Columns for Multi Column Data

Multi Column data before vs after applying Unpivot

  • 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-
    1. Go to the Home tab in the ribbon
    2. Select the Split Column option here
    3. 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
      Split Multi Column Data
    4. 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.)
    5. 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
    6. Click on OKData table after splitting Multi column data
  • 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-
    1. Select the column Attribute2 to Pivot the Multi-Column Data
    2. Go to the Transform tab in the Power Query Editor window
    3. Click on the Pivot Columns optionPivot Multi Column Data
    4. A Power BI ‘Pivot Column Window’ will get open. Select the Value column from the Dropdown list
    5. 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.
    6. Click on OK
      Data table after Pivoting Multi column data
  • 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.Rename Multi Column dataIn 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.

Simple Data

Simple data has a simple table structure. It contains a single column header for each column. Let’s learn how to Unpivot this data.

Multi-Column Data

Multi-Column data consists of complex data structure. It has combined column header for each column.

Dual - Row Header

It is the data structure that has two column headers for each column. One is the master header and second is the sub-header.

Multi - Row Header

It is the data structure that has multiple column headers. The column headers are split over multiple rows and columns.

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.

Crosstab vs Unpivot Dual Row Header Data

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

Import Data in Power BI

  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 WindowLoad Data in Power BI
  • 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
      Transpose Dual Row Header Data
  • 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
      Fill Down Dual Row Header Data
  • 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

      Merge Columns for Dual Row Header Data
    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
      Dual Row Header Data after Merging the Columns
  • 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
      Transpose after Merging the Dual Row Header Data
  • 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

Change Column Header for Dual Row Header Data

  • 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
      Unpivot Columns for Dual Row Header DataDual Row Header Data before vs after applying Unpivot
  • 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’
      Split Dual Row Header Data
    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 OKDual Row Header Data after Splitting the Columns
  • 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
      Pivot Dual Row Header Data
    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

Dual Row Header Data after applying Pivot

  • 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.
      Rename column headers for Dual Row Header DataThis is how you Convert the Dual Row Headers into normal Column Headers and Unpivot the data effortlessly.

Simple Data

Simple data has a simple table structure. It contains a single column header for each column. Let’s learn how to Unpivot this data.

Multi-Column Data

Multi-Column data consists of complex data structure. It has combined column header for each column.

Dual - Row Header

It is the data structure that has two column headers for each column. One is the master header and second is the sub-header.

Multi - Row Header

It is the data structure that has multiple column headers. The column headers are split over multiple rows and columns.

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

Crosstab vs Unpivot Multi Row Header Data

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
      Import Data in Power BI
  • 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

Load Data in Power BI

  • 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
      Transpose Multi Row Header Data
      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
      Fill Down Multi Row Header Data
  • Right Click on Column3 and click on the Remove Option. This will remove the Column3 which contains the Null values

Remove Column for Multi Row Header Data

  • 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
      Fill Down after Removing column for Multi Row Header Data
  • 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.
      Remove Top Rows for Multi Row Header Data
  • 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
      Change Column Header for Multi Row Header Data
  • 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
      Unpivot Columns for Multi Row Header DataMulti Row Header Data after applying Unpivot
  • 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 optionMove columns for Multi Row Header Data
  • 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
    Rename column headers for Multi Row Header Data
  • 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
      Pivot Multi Row Header Data
    4. Select the value column
    5. Select the Sum option from Aggregate Value Function
    6. Click on OK
      Multi Row Header Data after applying PivotHere, 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
Tags: