Table.CombineColumns
Joins several columns into one new column, using a combiner such as Combiner.CombineTextByDelimiter.
Table.CombineColumns(table as table, sourceColumns as list, combiner as function, column as text) as tableReturns: 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
- table: the table to change. Required.
- sourceColumns: a list of column names in braces, in the order to join them. Required.
- combiner: a function that joins the values. The common choice is
Combiner.CombineTextByDelimiter("-"), where the text in quotes is the separator. Required. - column: the name of the new column, in quotes. Required.
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
- The combiner needs text. Convert number and date columns to text first, as in the example.
- The source columns are removed. Duplicate them first if the reviewer needs them.
- Blank cells can leave extra separators in the result. Check a few blank rows and clean them with Text.Trim if needed.
- Query folding: this step usually does not fold. Power Query combines the values itself.
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.