Downloads
Power Query MTable: columnsIntermediate

Table.CombineColumns

Joins several columns into one new column, using a combiner such as Combiner.CombineTextByDelimiter.

Syntax
Table.CombineColumns(table as table, sourceColumns as list, combiner as function, column as text) as table

Returns: The table with the new combined column. The source columns are removed.

What it does

Table.CombineColumns takes several columns and merges their values into one text column, with a separator you choose. It is used to build a single key, such as account plus cost center, or to join a first name and last name into a full name. The original columns are removed in the result, so keep a copy first if you need them.

Syntax and arguments

Example

A general ledger has a separate Account and Cost Center column. A single key, such as "4000-CC10", is needed to match the ledger to a budget file that uses the combined code.

let
    Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    AsText = Table.TransformColumnTypes(Source, {{"Account", type text}, {"Cost Center", type text}}),
    WithKey = Table.CombineColumns(AsText, {"Account", "Cost Center"}, Combiner.CombineTextByDelimiter("-"), "Key")
in
    WithKey

The two columns are converted to text first, so the combiner can join them. The result is one Key column, and the original two columns are gone.

Example: a second, different use

A payroll file has first name and last name in separate columns. A full name is needed for a list of approvers.

let
    Source = Excel.CurrentWorkbook(){[Name = "Employees"]}[Content],
    FullName = Table.CombineColumns(Source, {"First Name", "Last Name"}, Combiner.CombineTextByDelimiter(" "), "Full Name")
in
    FullName

Things to watch

In business terms

A combined key lets you match two files that record the same thing in different layouts, such as a ledger and a budget. Building the key in the query, and documenting its rule, avoids errors from hand-typed codes.

Related functions

All Power Query M functions · View as Markdown · M basics