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.
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.Reo
rderColumns.
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!
@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
Thanks for sharing, Sam!
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.Hi Kortney! Can you post a code sample that reproduces the problem?
If it helps, the below works for me:
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!
Thank you for your kind words! I am glad the article helped!
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.
Hi Ben,
What does
?? {}
mean in row 7Hi Chandeep,
??
is the null coalescing operator. Control Structure (Primer Part 14) talks a little about it.ReorderRight = columnsToOrderRight ?? {}
sets ReorderRight to the value of columnsToOrderRight, unless that value is null, in which case columnsToOrderRight is set to{}
(an empty list).