How to unpivot data in Excel with Power Query
Power Query is a data management tool that helps change the layout or other properties of data easily. In this guide, we're going to show you how to unpivot data in Excel with Power Query.
Unpivoting means changing the rows and columns of your data. Follow the steps below to unpivot.
- Select a cell or the table containing the data you want to unpivot.
- Click Data > From Table/Range in the Ribbon to open Power Query Editor.
- Select the columns you want to unpivot by clicking on the column titles. Use either Shift or Ctrl key to select multiple columns at once.
- Click Unpivot Columns in Transform
- Power Query automatically populates all data under 2 columns:
- Attribute: Column names that repeat based on “not unpivoted” columns
- Value: Values corresponding to column names under the Attribute column
- When you are satisfied with the unpivoted data, click the Close & Load icon in Home tab to load the data into your worksheet.
- The unpivoted data will be populated in a new worksheet. You can edit your query by selecting Edit in the right-click menu.
If you are unfamiliar with the Power Query feature, you can always check our Power Query 101 article. The Power Query is a powerful tool allows you to search for data sources, create connections between them, and transform & shape that data.
