Excel Power Query Transpose Data: Steps & Tips

How-to basics · Updated November 22, 2026

To transpose data in Excel Power Query, load your table into Power Query (Data > From Table/Range), select the columns or rows you want to flip, then go to Transform > Transpose. This swaps rows and columns instantly, and the result loads back into Excel as a new table. Unlike copying and pasting with Paste Special, Power Query creates a live connection so you can refresh the transposed data whenever the source changes.

What Does Transpose Mean in Power Query?

Transposing flips the orientation of a table: the first row becomes the first column, and the first column becomes the first row. In Excel, the classic way is to copy a range, right-click, and choose Paste Special > Transpose. However, that method is static—if the original data changes, you must repeat the copy and paste. Power Query offers a dynamic alternative: you transform the data once, and every refresh re-applies the transpose to the current source.

Power Query’s Transpose button is found on the Transform tab in the Power Query Editor. It works on the entire selected range of columns and rows. For example, if you have quarterly sales data with months in rows and regions in columns, transposing will put regions in rows and months in columns.

How to Transpose Data in Power Query: Step-by-Step

Here is the exact process for Microsoft 365 on Windows. The steps are the same on Mac, but the ribbon paths are slightly different (Data tab > From Table/Range is under the Data menu).

  1. Select any cell in your data range and press Ctrl+T to create an Excel table (or use Home > Format as Table). This ensures Power Query can read the data as a table.
  2. Go to Data > From Table/Range. The Power Query Editor opens with your data loaded.
  3. If your data has headers, ensure the first row is recognized as headers (Home > Use First Row as Headers). If not, skip this step.
  4. Click anywhere inside the data preview, then go to Transform > Transpose. The rows and columns swap immediately.
  5. Optionally, you can promote the new first row to headers again (Home > Use First Row as Headers) if your transposed data needs a header row.
  6. Click Close & Load (Home > Close & Load) to load the transposed table into a new worksheet. To update later, right-click the loaded table and choose Refresh.

If you only want to transpose a portion of the data, select specific columns before clicking Transpose. Power Query will only transpose the selected columns, leaving others unchanged.

When to Use Power Query Transpose vs. Paste Special

Choosing the right method depends on your need for automation and the data structure.

  • Paste Special Transpose is best for one-off tasks where the source data won’t change. It is quick and requires no setup.
  • Power Query Transpose is ideal when your source data updates regularly (e.g., a daily export from another system). You refresh once and the transposed table updates automatically.
  • Power Query also handles large datasets better—it can process millions of rows without slowing down Excel.
  • If your data comes from an external source like a CSV or database, Power Query can connect directly to that source, transpose, and load—no manual copying needed.

Common Problems and How to Fix Them

Several issues can trip up users when transposing in Power Query.

  • Headers become data: If you transpose before promoting headers, the original headers become the first row of data. Fix by using Home > Use First Row as Headers after transposing, or by promoting headers before transposing.
  • Mixed data types: Transposing can combine numbers and text in one column, forcing Power Query to change the column type to ‘Any’. This can cause errors in later steps. Solution: after transposing, manually set the data type for each column via the type icons next to the column headers.
  • Blank cells: If your original data has blanks, transposing may shift values incorrectly. Use Transform > Fill > Down or Up before transposing to fill gaps.
  • Multiple tables: If you have several separate tables that need transposing, you must load each one separately. Power Query does not transpose multiple tables at once.

Power Query Transpose vs. Other Methods

Besides Paste Special, there are other ways to flip data in Excel. The TRANSPOSE function (entered as an array formula with Ctrl+Shift+Enter) creates a dynamic array that updates when the source changes, but it requires the source range to be fixed and can be tricky to edit. Power Query is more flexible because it can handle variable row counts and external connections. For example, if you add rows to the source table, Power Query picks them up on refresh, whereas the TRANSPOSE function would need its range expanded manually.

Another alternative is using Excel’s new dynamic array functions like =TRANSPOSE(A1:C3) in Microsoft 365, which spills automatically. But again, it only works with a static range. Power Query is the only built-in method that combines transposing with data cleaning, merging, and refreshing from files or databases.

Advanced Tips: Transposing with Unpivot

Often, users confuse transposing with unpivoting. Transpose flips rows and columns; unpivot turns wide data into a tall, normalized format. Sometimes you need both: first unpivot to get a long list, then transpose to reorient it. For instance, if you have monthly columns (Jan, Feb, Mar) and you want them as rows, you would select the month columns and choose Transform > Unpivot Columns, then transpose if needed. This is a common pattern in data cleaning workflows.

Another tip: use the ‘Use First Row as Headers’ after transposing to keep your data tidy. If your original data had no headers, you can add them manually before transposing by inserting a row with placeholder names, then transposing, then renaming the new headers.

Frequently Asked Questions

Can I transpose data in Power Query without creating a table first?
No, Power Query requires a table or a named range. If your data is in a normal range, select it and press Ctrl+T to convert it to a table. Alternatively, you can use Data > From Sheet, but that also requires a table.

Does transposing in Power Query change my original data?
No. Power Query loads a copy of the data into the editor. Your original Excel data remains untouched. The transposed result is loaded as a new table or worksheet.

Why does my transposed table show ‘null’ values?
Null values appear when the original data has empty cells. You can replace them by selecting the columns and using Transform > Replace Values, or by filling down/up before transposing.

Can I transpose data from a CSV file without opening it in Excel first?
Yes. Go to Data > Get Data > From File > From Text/CSV, choose your file, and it opens in Power Query. Then apply Transpose and load. This is a great way to handle large files that might be too big for a worksheet.

Is there a keyboard shortcut for transpose in Power Query?
No dedicated shortcut exists. You can use Alt+APT (press Alt, then A, then P, then T) on Windows to access Transform > Transpose, but it’s often faster to just click the button.

Can I transpose only every other column?
Yes. In Power Query, hold Ctrl and click the column headers you want to transpose, then right-click and choose Transpose. Only the selected columns are flipped.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.