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

  Function = 
    (data as table, columnsToOrderLeft as list, optional columnsToOrderRight as list) as table => 
      CurrentOrder = Table.ColumnNames(data),
      ReorderLeft = columnsToOrderLeft,
      ReorderRight = columnsToOrderRight ?? {},
      OrderedColumnsRemoved = List.RemoveItems(CurrentOrder, ReorderLeft & ReorderRight),
      NewOrdering = ReorderLeft & OrderedColumnsRemoved & ReorderRight,
      Reordered = Table.ReorderColumns(data, NewOrdering)
  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)

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.

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

Hope this helps!

Relationship Columns and Their Names

, ,

Have you ever stopped to think about relationship columns: how they work, when they’re automatically added, and in particular how they’re named?

On that last point: Did you know there is a latent danger where seemingly unrelated changes can break existing M code?

What Is a Relationship Column?

In a nutshell, a relationship column is an automatically added nested join between the table you’re working with and a related table. In the relationship column, for each row, there’s a nested table containing the associated rows from the related table. Thanks to M’s laziness, if the nested join isn’t used, fetching the related table’s row data will be skipped—so the presence of a relationship column whose values are unneeded does not incur an appreciable cost.

Continue reading

Describing a Function’s Record Parameters


Did you know that Power Query provides a way to define field-by-field details for a function’s record parameters?

You may already be familiar with how metadata can be used to provide descriptive details about a function and its parameter list. When provided, Query Editor uses this information to enhance the function invocation forms it generates and the IntelliSense it displays.

However, for record parameters, what’s not so well known is how to go beyond simply describing the basic details of a parameter to detailing its expected shape—such as the list of fields the record may (or must) contain, a friendly name for each field and even the allowed values on a per-field basis.

Pulling this off is easy!

Continue reading

Dataverse Web API Tip #13: Templates for New Entities

When creating a related record in Microsoft Dynamics/Power Apps, ever notice how the “quick create” form for the new entity may be displayed with some fields prepopulated?

Screenshot of Power Apps contact quick create form

For example, on an account, go to related contacts and click “New Contact.” By default, the create contact form that pops up shows account, phone and address pre-filled in with data from the account. The user can leave these values untouched or edit them, at their preference, before clicking “Save” to actually create the new entity record.

If you’re working programmatically—say using the Dataverse Web API—and would like to prepopulate fields like this from another entity, how would you go about pulling this off?

You could maintain your own mappings, like “When creating a contact for an account, set fields x, y and z on the new contact to the corresponding values from the related account.” While technically valid, this requires you to own the responsibility of configuring and storing these mappings. What if, instead, you could simply use the exact same template that the Dynamics/Power Apps UI uses?

Continue reading

Always Identical Outputs? Switch Statement vs. Switch Expression

, ,

Are these two statements identical in what they return?

public object Switcher(bool flag) 
  switch (flag) {
    case true:
      return 1;
    case false:
     return 10.5;
public object Switcher(bool flag) 
  return flag switch {
    true => 1,
    false => 10.5


TestResult –
Switch Statement
Result –
Switch Expression
Assert.Equal(1, x.Switcher(true));PassFail – Error Message:
Assert.Equal() Failure
Expected: 1 (System.Int32)
Actual: 1 (System.Double)
Assert.Equal(10.5, x.Switcher(false));PassPass

Scratching your head trying to find the difference? The logic in both looks identical. Arguably, it is.

Continue reading

Dataverse: Same data everywhere? Dynamics/Power Apps UI + Web Services vs. TDS Endpoint + Power BI/Power Query + Azure Synapse Link

, ,

If you try accessing the same data using the various mechanisms listed below will you always receive back identical values?

It depends.

Continue reading

M Language Proposal: Cleaning Up Function Chains with the Pipeline Operator

, , ,

Sometimes, a chain of M function calls reads as a dense blob of code, yet refactoring to the clearer structure of a let statement is an overkill. Let’s look at an alternative, a new operator to consider for inclusion in the M language.

The Problem

You’d like to add a column to your customers table that holds the average amount of the given customer’s three largest completed orders. The needed data is already available in the table, thanks to a nested orders table. All that’s needed is for you to define logic that uses this data to calculate the desired average.

To pull this off, your new column’s logic needs to:

  1. Filter the nested orders table to Status = "Completed".
  2. Sort by Total, descending.
  3. Take the top 3 results.
  4. Average their Totals.
Continue reading

Power Query M Primer (Part 22): Identifier Scope II – Controlling the Global Environment, Closures

, , , ,

As we learned last time, normally, M code is evaluated in a global identifier resolution scope consisting of all shared members + the standard library. Also, normally, we can’t inject additional identifiers into this global environment. Normally isn’t always. Today, we learn about the exception: where both of these normalities do not apply.

That’s not all: Did you know that M has a mechanism for remembering how to access variables that later go out of scope? Closures open up powerful options, particularly when generating functions…and even enable building an object-like programmatic construct that maintains internal private state and is interacted with through a public interface (kind-of, sort-of somewhat like an object from object-oriented programming!).

Continue reading

M Mysteries: The Mysterious Type Action—An M-Internal Means to Write Data Modifications to External Systems

, ,

Power Query is great for reading, combining and computing data, but it’s not meant for writing data modifications—like inserts, updates or deletes—back to the source. Correct?

Yes and no.


If you are a non-internal user, then yes, Power Query is intended to be read only: it does not expose functionality meant for inserting, updating or deleting data on remote systems. But this doesn’t mean Power Query lacks this capability: to the contrary, it has hidden, internal support for performing data modifications!

Continue reading