fbpx
Email: [email protected] Call Now! +1 203-349-9909 / +91-8080042523

How to Unpivot Data Using Excel Power Query

Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within the Power BI Designer canvas.

Unpivot Data With Excel Power Query

One of the best features of the Power Query is to Unpivot Columns.

Unpivot Columns feature transforms columns with similar characteristics and puts them in a tabular format, which then helps you to do further analysis.

Let’s understand the Unpivot Columns feature with an example.

Step 1: Select Columns to Unpivot

When you load the data into Power Query, you get to see that the data table is not in proper order. To rearrange it, select the columns you want to Unpivot as shown below.

 SELECT COLUMNS TO UNPIVOT

Step 2: Unpivot Columns

To Unpivot columns, go to

Transform >Unpivot Columns >Unpivot Only Selected Columns

Note: You can directly click on the Unpivot Columns button if you want to transform the entire data table.

UNPIVOT COLUMNS

After you Unpivot the data, the resulted data table will be in a vertical format as shown in the below picture.

DATA TABLE AFTER UNPIVOTING COLUMNS

Step 3: Rename Column Headers

The column headers after you Unpivot the columns are by default set as Attribute and Value. If you want to change these names, just double click on column headers and edit it. Below picture shows the Unpivot data table with renamed column headers.

AFTER RENAMING COLUMN HEADERS

You can acquire more knowledge :

Leave a Reply