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!
Great technique, which I use a lot!
Nice trick!
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).
Fabulous!