Resilient Relative Column Reordering

, , ,
Screenshot showing the "Move > To Beginning" option in Query Editor

There’s this one column you’d always like to appear leftmost in the table. No problem! In Query Editor, you right-click on the column and choose Move > To Beginning, which generates a “Reordered Columns” step for you.

All is well, until down the road when you remove a different, seemingly unrelated column from the table. Your Power Query refreshes start failing, complaining that the removed column is not found.

Column not found error message

You dig into the problem and find that the reordered columns step that Query Editor generated included a hard-coded reference to the now-removed column. To get things working again, you must hand edit this step’s M expression, manually removing the problematic column reference.

Why? Why did you need to remove (by hand, nonetheless!) a reference to a column that you didn’t consciously put there—a reference to a column whose position you didn’t ask Query Editor to reorder?

Relative-less (how sad!)

As it turns out, Table.ReorderColumns—the function that powers Query Editor’s reorder columns feature—does not support relative reordering. Conceptually, you wanted a column reordered to be the table’s first column, but Table.ReorderColumns doesn’t provide a way to simply say “make this one column leftmost”. Instead, any column reordering performed in the UI generates a function call to that method where it’s passed a list of all columns in the table, each in their desired order.

#"Reordered Columns" = Table.ReorderColumns(Source,{"ID", "FirstName", "LastName"})

If one of these columns is later removed, Query Editor doesn’t automatically update the passed in column list, so your code breaks. Ouch! In contrast, adding a new column to the table doesn’t cause Table.ReorderColumns to fail, but this doesn’t mean the experience is painless: the presence of the new column may bump the column you wanted leftmost out of that position.

It would be nice to eliminate these pain points.

Make Do…

Table.ReorderColumns has an optional third argument which can be set to MissingField.Ignore. This suppresses the missing column name error, which keeps the function working even though the column is gone. While this works, it leaves the deleted column’s name in code (code clutter = undesirable). It also doesn’t guarantee that the column you want on the left will stay there when new columns are added to the table.

Surely there’s a better way to do relative reordering….

…Or, Do It Nice!

Let’s see. The pain point is the hard-coded column list that’s passed to Table.ReorderColumns. We’re M code writers. Why don’t we use code to dynamically compute that list and perform the reorder?! We could craft a function that takes a list of just those columns we want leftmost, which then dynamically fetches the table’s current column list and adjusts its order appropriately before passing the result to Table.ReorderColumns.

Something like the below (which includes the bonus feature of also supporting rightmost relative ordering):

let
  Function = 
    (data as table, columnsToOrderLeft as list, optional columnsToOrderRight as list) as table => 
    let
      CurrentOrder = Table.ColumnNames(data),
      ReorderLeft = columnsToOrderLeft,
      ReorderRight = columnsToOrderRight ?? {},
      OrderedColumnsRemoved = List.RemoveItems(CurrentOrder, ReorderLeft & ReorderRight),
      NewOrdering = ReorderLeft & OrderedColumnsRemoved & ReorderRight,
      Reordered = Table.ReorderColumns(data, NewOrdering)
    in
      Reordered,
  FunctionType = 
    type function 
      (
          data as table,
          columnsToOrderLeft as (type {text}), 
          optional columnsToOrderRight as (type {text})
      ) 
      as table
      meta [
        Documentation.Name = "TableRelativeReorderColumns", 
        Documentation.LongDescription = "Returns a table from the input <code>table</code>, with the columns in <code>columnsToOrderLeft</code> appearing leftmost in the order given and the columns in <code>columnsToOrderRight</code> appearing rightmost in the order given. Other columns will not be reordered."
      ],
  Ascribed = Value.ReplaceType(Function, FunctionType)
in
  Ascribed

No more need for a hardcoded list of all column names. No more code clutter when MissingField.Ignore is used and a column is removed. Columns stay in the expected relative order even when new columns are added.

let
  TableRelativeReorderColumns = (code from above),
  Source = ...,
  Reordered = TableRelativeReorderColumns(Source, {"ID"})
in
  Reordered

Hope this helps!

9 thoughts on “Resilient Relative Column Reordering

  1. sam

    @Ben this is super useful – I use to follow a slightly different approach in my Excel Apps.
    Create a Table of one or more rows with Column names that you want to appear “Left most”
    Convert it in to list after importing it in to PQ – and use the list in Table.ReorderColumns

    Cheers
    Sam

    Reply
  2. Kortney Jendro

    Hi Ben, think this code is exactly what I’ve been looking for, but I’m trying to have an index column reordered to the furthest left. It’s giving an error when invoking the function – cannot convert to text.

    Tried changing the “as (type {text})” line to “as (type {number})“. But still errors out.

    Reply
    1. Ben Gribaudo Post author

      Hi Kortney! Can you post a code sample that reproduces the problem?

      If it helps, the below works for me:

      let
          Source = #table({"Name"}, {{"Joe"}}),
          AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
          Reordered = TableRelativeReorderColumns(AddedIndex, {"Index"})
      in
          Reordered
      Reply
  3. James Northrop

    Kudos to you. While I’m new to Power Query and haven’t built custom functions in M before, your explanation was clear and I got the code to work within 30 minutes. Thanks!

    Reply
  4. John Keegan

    The column order in Power Query is not necessarily replicated in the data model. Tables imported to a Power BI data model retain the original order. New columns added after the very first import are added to the end. Even if you change the order in Power Query from within the Power BI pbix file, the imported table inside Power BI does not mirror the column order.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *