Re-ordering columns

Overview

Often, with ETLs, you are not concerned about the exact order of columns but would like to apply a specific order to a smaller subset of columns.

By default, in power query, when you reorder columns, the Table.ReorderColumns function is called with a reference to all the columns in the table.

Whilst auto-generating code can be helpful, referencing all columns can really weaken an ETL and increase the chances of future refresh failures, when, or if columns are renamed or removed from the source data.

If re-ordering is needed, it is better to apply it after you keep only the columns needed in the report.

In this article we show different ways of re-ordering just a sub-set of columns, with data of the following form:

With this data, you wish to move the “City” and “Revenue” columns to be the 2nd and 3rd columns.

This would be done as follows:

Steps

1. Load the data to the Query Editor

2. Add ‘M’ code

Next, click on the fx button, and insert the following code:

= Table.ReorderColumns(
    Source, List.InsertRange(
        List.Difference(
            Table.ColumnNames(Source), 
            {"City", "Revenue"} ),
             1,
             {"City", "Revenue"}
        )
    )

The code uses the List.InsertRange function to insert “City”, and “Revenue”, at index position 1, of the list returned by the function List.Difference - which returns all column names in the table, except “City” and “Revenue”.

The result is a list that looks like {Date, City, Revenue, Color, Supplier, Category}, (in that order!), which is just what is needed by the Table.ReorderColumns function.