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. The power Query is an advanced excel online function that enables you to connect, discover, refine and combine data sources to meet your data analysis needs.
Unpivot Data With Excel Power Query
One of the best features of the Power Query is 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.
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.
After you Unpivot the data, the resulted data table will be in a vertical format as shown in the below picture.
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.
You can acquire more knowledge :
- Steps to Filter Records in Excel
- Learn How to Import Web Data in Excel
- Consolidate Multiple Excel Sheets Using Power Query
- Consolidate Multiple Excel Workbooks in Power Query