Excel · Power BI · Power Query

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 thoughts on “Reordering Multiple Columns With ‘Remove Other Columns’ In The Power Query Editor

  1. Fred Kaffenberger – Since I discovered custom reporting, I've championed responsive analytics at the service of business. As a data and reporting pro, I solve challenging business cases, convert reports from legacy systems, and tell compelling stories with dashboards.
    Fred Kaffenberger says:

    Great technique, which I use a lot!

  2. Ivan Bondarenko – Moscow – Excel enthusiast, data / business / system analyst, VBA / SQL / Power Query developer, huge fan of Excel modeling (PowerPivot, finance, supply chain, marketing etc.).
    Ivan Bondarenko says:

    Nice trick!

  3. Chris Haas – Minnesota, USA – I am a business intelligence consultant that specializes in optimizing data models, tuning DAX, and simplifying visualizations to drive action.
    Chris H says:

    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).

Leave a ReplyCancel reply

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