Bottom line: Learn a few quick tips to quickly reorder multiple columns in Power Query without having to move each column individually.
Skill level: Beginner
Reordering a bunch of columns in Power Query can be a drag (literally). You might spend extra time moving each column into place. This can be especially time-consuming if you have a lot of columns that you have to drag and scroll across a large table.
In this post I share a few tips to make the process of reordering columns faster.
Download the Excel File
You can download the example file to follow along and practice.
1. Reorder Multiple Columns with Ctrl+Select
The easiest way I've found to reorder multiple columns is to select the columns with the Ctrl key and then move them. Here are the instructions.
1. Hold the Ctrl key and select each of the columns in the order you want them displayed. In this example I want the columns to be displayed in the following order, so I select each column in that order.
Order ID, Order Date, Category, Product Name, Unit Price
2. Left-click and hold any one of the columns and drag the mouse to the location you want to place the columns.
3. Release the mouse and the columns will be reordered based on the order you selected them in step 1.
Important Note: If you end up selecting all of the columns, you will need to drag the selection to the right of the last column, then release. If you drag to the left of the columns, nothing will happen.
2. Reverse Order of Columns
You can use this same basic technique to quickly reverse the order of all (or some) columns.
1. Select the last column in the table, hold the Shift key, and then select the first column in the table.
2. Left-click and hold the last column and drag the mouse to the right of the last column.
3. Release the mouse and the column order will be reversed.
Note: You can also use this same technique for reversing the order of just some of the columns in your query. The process is the same, you will just select a few columns within the table. You can also drag the columns to the left if the first column is not part of the selection.
3. Undo or Change the Order
If you realize that the new column order isn't exactly what you wanted, there are a few ways to fix it.
Undo the Reorder
First, you can undo the change by deleting the Reordered Columns step in the Applied Steps window. Then use the select and move technique again with the correct order.
Check out my post on 15 Shortcuts for Power Query to learn how to undo (relatively) quickly.
Manually Refine the Order
If just a few columns are out of order, you can also manually select and drag individual columns to move them. If you do this step directly after the previous step above, then the Reorder Columns step will be modified in the Applied Steps window.
Power Query is smart enough to NOT add additional steps for the reordering, which makes it easier to delete the step if needed.
Modify the Formula Text
Another option is to modify the formula text in the Table.ReorderColumns function that is created for the Reorder Columns step. To me, moving the columns with the mouse is easier because you don't have to worry about getting the syntax with the commas and quotation marks perfect (any typos in the syntax will result in an error).
However, it's still good to know about this technique because you might have the column order in another file, report, or list somewhere, and you can use that text to quickly modify the column order. This can be especially useful if your table has dozens or hundreds of columns.
Free Webinar on Power Query, VBA, & More
If you're new to Power Query, I have a free webinar running right now that covers an introduction to Power Query and the other Excel tools like Power Pivot, Power BI, Macros & VBA, pivot tables, and more.
Click here to register for the webinar
Conclusion
I hope these quick tips save you time when rearranging columns in Power Query. Please leave a comment below with any additional tips, questions, or suggestions. Thank you! 🙂
GR8 Jon. This saves me a lot of time. Thanks!
Thanks Edil! I’m happy to hear it. And it’s great to hear from you! 🙂
Another way of “automatically” rearranging columns in PowerQuery is to select the columns in the wished order while holding Ctrl key. Then hit the button “Remove other columns”, this will create a Table.SelectColumns step and display the columns in the selected order. Omitted columns will be automatically deleted but the selected order will be still retained.
Great tip Michael! Thanks for sharing! 🙂
I know I should be able to do this based on the simple instructions but for some reason, it is not working for me. Do you happen to have a video (I know it sounds silly to even ask)
Hi Jon,
I discovered your blog only a few weeks ago, and I really appreciate your refined quick tips. I haven’t used the Power Query as much as I should since I rely on formulas. The ability to re-order columns is beneficial. I agree that editing the formula syntax is complicated since an extra space could create issues. The file you provided as a download is appreciated. I would love to see some of your skills with graphing from Power Query Tables. I find there are some limitations versus using commonly formatted data.
-Krist