Reordering Multiple Columns With ‘Remove Other Columns’ In The Power Query Editor

In the comments to my last blog post on how the order that you select columns can affect the output of certain calculations, both Bradley Sawler and John B. Thomas pointed out something very useful that I didn’t know about: that the order you select columns can also be used with the ‘Remove Other Columns’ functionality to reorder columns in bulk.

For example, imagine you have a table with columns called A, B, C, D, E and F. If you select the columns in the order F, E, D, A, B and C and the select ‘Remove Other Columns’, the columns are reordered in the order that you clicked them:


As you can see from the demo above, ‘Remove Other Columns’ uses the Table.SelectColumns M function behind the scenes and the order the columns are listed in that function is the order that you have clicked them in. A great trick for reordering a large number of columns quickly!

5 responses

  1. One small caveat when using this. If you manually rearrange the order of the list of the columns (to get the order you prefer), but then click on the gear to add/remove columns using the UI…you’ll get the columns in the default order.

    So if you want to preserve the order while making adjustments to what is kept/removed, you need to make those adjustments in the Advanced Editor (or formula bar).

  2. Pingback: Power BI, Excel, USAFacts and more... (April 19, 2018) | Guy in a Cube

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: