Mrinmoy Roy
You can convert columns into rows or rows into columns in many ways. But that might take a long time as well as more effort to do. Besides, that may not facilitate you that much flexibility. On the other hand, Excel has embedded a feature called Power Query into it. That is powerful as well as offers a lot more flexibility and features. In this tutorial, you will learn to convert columns to rows in Excel using Power Query.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
What is Power Query in Microsoft Excel?
Power Query is a very powerful tool in Microsoft Excel 2010 and later. It can clean up and automate data in Excel. The Power Query is also known as “Get & Transform Data”. So the primary purpose of the Power Query can be divided into two main categories,
- Get Data &
- Transform Data
Get Data: You can retrieve data from anywhere into Excel such as Databases, other Excel files, or web pages. This is a powerful feature to input raw data into the Excel sheet.
Transform Data: After importing data, you can use this tool to clean up your data by rearranging them. You can add more columns or merge columns and so much stuff like that.
Get Power Query to Convert Columns to Rows in Excel Using Power Query
The Power Query is only available in Excel for the Windows pcs. However, all the versions of the Excel application don’t support this feature. You can use the Power Query only in Excel 2010 and later versions.
If you are using Excel 2010 or Excel 2013, then you have to Download Power Query from the official website of Microsoft. Then you need to use that as an Excel Add-in.
But if you are using Excel 2016 or later versions, you don’t need to worry about downloading Power Query. You will find it in Excel as a built-in feature.
Read More: Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)
Format Data as a Table to Convert Columns to Rows in Excel Using Power Query
The following screenshot reveals the dataset that I’m going to use to demonstrate the conversion process from columns to rows in Excel using Power Query.
First of all, we need to convert this dataset into an Excel table to apply Power Query. To do that,
❶ Click on a cell of the dataset.
❷ Then go to the Home tab of the main ribbon.
❸ After that click on the Format as Table drop-down.
Now you will see a lot of table formats.
❹ Select any of them you like to apply to your data table.
❺ Next, the Create Table dialog box will appear.
You will see the cell range is already there.
❻ Finally, hit the OK button to create an Excel Table.
Read More: How to Transpose a Table in Excel (5 Suitable Methods)
Similar Readings
- How to Transpose Columns to Rows In Excel (6 Methods)
- How to Transpose in Excel (5 Easy Ways)
- How to Convert Column to Comma Separated List With Single Quotes
- VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)
- How to Convert Multiple Columns into a Single Row in Excel (2 Ways)
Use Power Query to Convert Columns to Rows in Excel
As we are done with formatting the data table as an Excel table, we are ready to apply the Power Query. To do that,
❶ Click on any cell of the data table to select a cell.
❷ Then go to the Data tab.
❸ Under the Get & Transform data group, click on From Table/Range.
After that, a new window called Power Query Editor will appear.
❹ From the new window, select the columns that you want to convert to rows.
❺ Then go to the Transform tab.
❺ From the Any Column group, click on Unpivot Columns.
After applying Unpivot Column, you will see your columns has been converted to rows as in the picture below:
Read More: How to Transpose Columns to Rows In Excel (6 Methods)
Data Live Update in the Excel Table
As we are transposing using the Power Query, it enables us to instant insert and update in Excel data tables.
All we need to do is,
❶ Go back to the Home tab of the Power Query Editor.
❷ From the Close group, choose Close & Load.
❸ Excel will create a new worksheet to display the converted version of the raw table.
Here in this table, you will see the columns are converted into rows.
❹ Now you can update the original data table, as I did in row 15.
❺ Now right-click anywhere on the newly created Excel table using the Power Query.
❻ From the pop-up list, choose Refresh.
After refreshing the worksheet, you will see that the data table is updated.
The interesting fact is that the newly added data have already converted from columns to rows as in the screenshot below:
Read More: How To Transpose Data in Excel (4 Methods)
Conclusion
To sum up, we have discussed how to convert columns to rows in Excel using the Power Query in easy steps. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
Related Articles
- VBA to Transpose Array in Excel (3 Methods)
- How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)
- Excel VBA: Transpose Multiple Rows in Group to Columns
- How to Transpose Rows to Columns Based on Criteria in Excel
- How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)
- How to Transpose Rows to Columns in Excel (5 Useful Methods)
- How to Transpose in Excel VBA (3 Methods)
