Table.CombineColumns

Updated on

Table.CombineColumns is a Power Query M function that combines specified columns into a new column using a provided combiner function. The function returns a new table with the combined column.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.CombineColumns(
   table as table,
   sourceColumns as list,
   combiner as function,
   column as text,
) as table
ArgumentAttributeDescription
tableThe source table containing the columns to combine.
sourceColumnsThe columns to combine provided as a list, e.g.: { “Column1”, “Column2” }
combinerContains the logic to combine values. You can provide a custom combiner function, or use any of the available standard library combiner functions. For instance:
‎  ‎‎  • Combiner.CombineTextByDelimiter
‎‎    • Combiner.CombineTextByEachDelimiter
‎    • Combiner.CombineTextByLengths
‎    • Combiner.CombineTextByPositions
‎    • Combiner.CombineTextByRanges
columnThe column name of the newly created column containing the combined values.

Description

Table.CombineColumns merges specified columns of a table into a single new column, using the defined combiner function.

Examples

Let’s see a few examples on how this can work. First, consider our sample dataset:

Dataset for Table.CombineColumns in Power Query M

Combining Columns with the UI

The Power Query Editor offers a user-friendly interface to combine columns without writing any code:

  • Select the columns
  • Go to Transform and select Merge Columns
  • In the pop-up that appears, use space as separator and name the column FullName
  • Press OK

This process generates the following M code snippet:

Table.CombineColumns(
  Source,
  {"FirstName", "LastName"},
  Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
  "FullName"
)

Here we combine the columns FirstName and LastName from the Source table. We use a space as a separator and use the Combiner.CombineTextByDelimiter function. The result? A combined column titled FullName in your table:

Table.CombineColumns combined table with data type set

Using a Custom Function

For those who prefer a more hands-on approach, Power Query M allows for custom functions to merge columns. Here’s an example:

Table.CombineColumns(
    Source, 
    {"FirstName", "LastName"},
    each Text.Combine( _ , " "),
    "FullName" 
)

This approach offers flexibility but unfortunately omits the data type for the new column, FullName. The below image is the result of using the above combiner function:

Table.CombineColumns combined table without data type set

Here’s how you can adjust the previous function to include the data type:

Table.CombineColumns(
    Source, 
    {"FirstName", "LastName"},
    (_) as text => Text.Combine( _ , " "),
    "FullName" 
)

By defining the data type, you can skip an additional step to change the data type. As this article showed, Table.CombineColumns allows you to easily combine columns using one of the combiner functions. Alternatively, you can write a custom function to combine values.

If you want to try this code out yourself, you can use the following code:

let
  Source = 
    #table( 
      type table[ FirstName = Text.Type, LastName = Text.Type ],
      {{ "Rick", "de Groot" },{ "Will", "Smith" },{ "Marc", "Anthony" }} ),
  Merge_CombinerFunction = 
    Table.CombineColumns(
      Source,
      {"FirstName", "LastName"},
      Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),
      "FullName"),
  CustomFunction_NoDataType = 
    Table.CombineColumns(
      Source, 
      {"FirstName", "LastName"},
      each Text.Combine( _ , " "),
      "FullName" ),
  CustomFunction_WithDataType = 
    Table.CombineColumns(
      Source, 
      {"FirstName", "LastName"},
      (_) as text => Text.Combine( _ , " "),
      "FullName" )
in
    CustomFunction_WithDataType

Other functions related to Table.CombineColumns are:

Contribute » | Contributors: Rick de Groot, Ben Gribaudo
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-combinecolumns