Table.CombineColumns

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.CombineColumns

What is Table.CombineColumns?

Table.CombineColumns is a Power Query M function that allows us to combine the values of two or more columns into a single column. This can be useful when we have data that is split across multiple columns and we want to combine it into a single column for analysis or reporting purposes.

How to Use Table.CombineColumns

The syntax for Table.CombineColumns is as follows:

Table.CombineColumns(table as table, column1 as text, column2 as text, separator as text, newColumnName as text)

`table`: The table that contains the columns we want to combine.

`column1`, `column2`: The names of the columns we want to combine.

`separator`: The character or text string that we want to use to separate the values in the combined column.

`newColumnName`: The name of the new column that will contain the combined values.

Here is an example of how we might use Table.CombineColumns:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1VrJQ0lEq1lEyNDXUNjQwNjA0MjB2MnFyMnUwNjA0MjB2MnFyMnUwMjAwNjA0MjB2MnFyMnUwNjA0MjB2MnFyMnUwMjAwNjA0MjB2MnFyMnUwNjA0MjB2MnFyMnUwMjAwNjA0MjB2MnFyMnUwNjA0MjB2MnFyMnUwMjAwNjA0MjB2MnFyMnUwNjA0MjB2MnFyMnUwMjAwNjA0MjB2MnFyMnUwMjAwNjA0MjB2MnFyMnUwMjAwNjEyMzRyNjAzNlqNzYCjbQ1mlKwEqAE=", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),

#"Combined Columns" = Table.CombineColumns(Source,{"Column2", "Column3"},", ","NewColumn")

in

#"Combined Columns"

In this example, we start with a table that has seven columns. We use the Table.CombineColumns function to combine the values in columns 2 and 3, using a comma and space as the separator. The resulting table has six columns, with a new column called “NewColumn” that contains the combined values.

The M Code Behind Table.CombineColumns

Here is the M code that is generated by the Power Query Editor when we use Table.CombineColumns:

let

Source = table,

CombineColumns = Table.AddColumn(Source, newColumnName, each Text.Combine({[column1], [column2]}, separator), type text),

RemoveColumns = Table.RemoveColumns(CombineColumns,{column1, column2})

in

RemoveColumns

Let’s break down what’s happening in this code:

1. We start by defining a variable called “Source” that represents the input table.

2. We then use the Table.AddColumn function to create a new column called “newColumnName”. We use the Text.Combine function to combine the values from columns 1 and 2, using the separator specified in the function arguments.

3. Finally, we use the Table.RemoveColumns function to remove columns 1 and 2 from the table.

This code is relatively simple, but it demonstrates the power of the M language. By using functions like Table.AddColumn, Text.Combine, and Table.RemoveColumns, we can create complex data transformations that would be difficult or impossible to achieve using traditional spreadsheet tools.

Table.CombineColumns is a powerful function in the Power Query M language that allows us to combine the values of two or more columns into a single column. By understanding the syntax and the underlying M code, we can use this function to create custom data transformations that meet our specific needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)