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:

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

= #table(
         {"Column1", "Column2", "Column3", "Column4"},
         {{"Don't merge me", "merge me with the next two columns", 2, 3},
          {"Don't merge me", "merge me with the next two columns", 3, 4},
          {"Don't merge me", "merge me with the next two columns", 4, 5}}
)

Now, let’s merge the last three columns. Select the last columns, right click on one of the headers and select Merge Columns.

In Merge Columns dialog, select Colon as Separator and click OK.

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

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.

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

let
    Source = #table(
        {"Column1", "Column2", "Column3", "Column4"},
        {{"Don't merge me", "merge me with the next two columns", 2, 3},
         {"Don't merge me", "merge me with the next two columns", 3, 4},
         {"Don't merge me", "merge me with the next two columns", 4, 5}}),

    #"Merged Columns" = Table.CombineColumns(
        Table.TransformColumnTypes(
            Source, {{"Column3", type text}, {"Column4", type text}}, "en-US")
        ,{"Column2", "Column3", "Column4"},
        Combiner.CombineTextByDelimiter(":", QuoteStyle.None),
        "Merged")
in
    #"Merged Columns"

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.

let
    Source = #table(
        {"Column1", "Column2", "Column3", "Column4"},
        {{"Don't merge me", "merge me with the next two columns", 2, 3},
         {"Don't merge me", "merge me with the next two columns", 3, 4},
         {"Don't merge me", "merge me with the next two columns", 4, 5}}),

    TransformColumnTypes = Table.TransformColumnTypes(
            Source, {{"Column3", type text}, {"Column4", type text}}, "en-US"),

    #"Merged Columns" = Table.CombineColumns(
        TransformColumnTypes,
        {"Column2", "Column3", "Column4"},
        Combiner.CombineTextByDelimiter(":", QuoteStyle.None),
        "Merged")
in
    #"Merged Columns"

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

let
    Source = #table(
        {"Column1", "Column2", "Column3", "Column4"},
        {{"Don't merge me", "merge me with the next two columns", 2, 3},
         {"Don't merge me", "merge me with the next two columns", 3, 4},
         {"Don't merge me", "merge me with the next two columns", 4, 5}}),
    
    Headers = Table.ColumnNames(Source),

    RangedHeaders = List.Range(Headers, 1, List.Count(Headers)-1),
  
    ColumnTypesList = List.Generate(
        ()=>[stateList=RangedHeaders, i=0],
        each [i]< List.Count(RangedHeaders),
        each [stateList=[stateList], i=[i]+1],
        each {[stateList]{[i]}, type text}),

    #"Changed Type" = Table.TransformColumnTypes(Source, ColumnTypesList),

    #"Merged Columns" = Table.CombineColumns(#"Changed Type", RangedHeaders, Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

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:

ColumnTypesList = List.Transform(RangedHeaders, each {_, type text}),

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

Hope you find it useful 🙂