Merging columns

Overview

When merging multiple columns into a single column, power query will transform any numeric columns into text, and then combine the columns together.

The following code, in such instances, will be automatically generated:

    #"Merged Columns" = Table.CombineColumns(
        Table.TransformColumnTypes(
                    Source, {
                            {"Column 2", type text}, 
                            {"Column 3", type text}}, 
                            "en-GB"
                        ),
                        {"Column 1", "Column 2", "Column 3"},
                        Combiner.CombineTextByDelimiter("", QuoteStyle.None),
                        "Merged"
                    )

The function, Table.TransformColumnTypes enforces the type conversion of the numeric columns (Column 2, and Column 3), then merges the relevant columns (Column 1, Column 2, Column 3)

Unfortunately, this auto-generated formula exposes the ETL to an unnecessary refresh failure, if for instance, the column names were to change in future.

Modify code

The problem can be overcome by modifying to merge the given columns without referencing any hard-coded column names.

If you first created a list, called ‘ColumnstoMerge’ with the following code:

= Table.ColumnNames(#"Table to Merge Source")

This will return the column names (assuming you want to merge all of them) as a list, like shown here:

If you then modify the original code that was generated to merge the columns, instead to this:

    #"Merged Columns" = Table.CombineColumns(
        Table.TransformColumnTypes(
            Source,
                List.Transform(
                    ColumnsToMerge,
                    each{_, type text}
                ),
                "en-GB"
                ),
                ColumnsToMerge,
                Combiner.CombineTextByDelimiter("", QuoteStyle.None),
                "Merged"
            )

You should get the same result, but the query is less likely to suffer from a future refresh failure.

List.Transform iterates over each column name in the list, ColumnsToMerge, and transforms it into a list of column names, with type text.

The code is a great example of how list functions can be used to write more scalable versions of complex ETL transformation functions.