Robust Queries: Convert column types and merge columns without referencing column names

Converting column types, and merging columns are common transformations in Power Query. It is so easy to perform these transformations through the Query Editor user interface (as we will demonstrate in a minute), but sometimes, the auto-generated Power Query expression that is created when you perform these transformation is not robust to new columns or changes in column names, and will fail on refresh.

There are many additional transformations that are column name dependent. Here are few more: Choosing columns, Grouping and Pivoting.

In all the cases above, we would like to change the Power Query (M) expression and be able to reference the columns by their location/index.

Let’s see in an example. We start with this simple table:

screenshot_1

Here is the formula you can use to create this table in the Query Editor Formula Bar.

Now, let’s merge the last three columns. Select the last columns, right click on one of the headers and select Merge Columns.
screenshot_3
In Merge Columns dialog, select Colon as Separator and click OK.

screenshot_3

And we are done. The column Merged contains the merged three columns with a colon as a separator.

screenshot_4

And here is the tricky part…

The auto-generated Power Query formula contains a code that is explicitly referencing the columns Column2, Column3 and Column4.

screenshot_5

The implications can be quite bad if you use the same operations on an external data source of the same format, and the column names changes (refresh will fail), or when you have new columns (columns will be missed in the transformation).

Let’s take a look in the entire expression (through the Advanced Editor):

Did you see how Power Query nested the type conversion step inside the merge (Table.CombineColumns) step?

For simplicity, let’s split the last transformation into two, and move the type conversion outside of the Table.CombineColumns.

Now, in the expression above we have two sections we would like to change.

The first section is the nested list that is used as the second argument of Table.TransformColumnTypes:

{{“Column3”, type text}, {“Column4”, type text}}

We can use List.Generate to dynamically create this list. You will see it in a minute. For simplicity we will also convert the second column to text (It’s type is already text, but the code will be simpler).

The second section is the list that is used as the second argument of Table.CombineColumns:

{“Column2”, “Column3”, “Column4”}

We will use a combination of the functions Table.ColumnNames, List.Range and List.Count to create the list above.

And below you can find the fixed expression. It starts with a ranged list which is built of the column names, and the statically defined range (in our example, a sub list starting from the second column till the end of the list).

Update: @TheBIccountant proposed a simplified version for ColumnTypesList that uses List.Transform instead of List.Generate. I am keeping my original version that uses List.Generate, so you’ll get examples of both methods. Here is the List.Transform version:

That’s it. I am sure you can use the same tequnique in many scenarios.

Hope you find it useful 🙂

2 comments

  1. Pingback: 10 common mistakes you do in #PowerBI #PowerQuery - And how to avoid pitfalls - DataChant

  2. Pingback: The Ten Pitfalls of the Data Wrangler (aka the Wielder of M/Power Query) - PowerPivotPro

Leave a Reply