Table.CombineColumns

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

Argument	Attribute	Description
table		The source table containing the columns to combine.
sourceColumns		The columns to combine provided as a list, e.g.: { “Column1”, “Column2” }
combiner		Contains 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
column		The 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:

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:

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:

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

Related functions

Other functions related to Table.CombineColumns are:

• Table.CombineColumnsToRecord
• Table.PrefixColumns
• Table.SplitColumn
• Table.TransformColumnTypes
• Table.TransformColumns
• Table.TransformRows

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