Combiner.CombineTextByPositions

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Combiner.CombineTextByPositions

Overview of Combiner.CombineTextByPositions

The Combiner.CombineTextByPositions function allows you to combine text values from different columns by specifying the positions of the columns. For example, if you have three columns (Column1, Column2, and Column3), you can use the function to combine the values in Column1 and Column3 into a single column, while ignoring the values in Column2.

The function takes three arguments:

– The table to operate on

– A list of positions (in the form of integers) that specify the columns to combine

– An optional delimiter to use between the combined values (default is a comma)

Here is the basic syntax of the function:

Combiner.CombineTextByPositions(table as table, positions as list, optional delimiter as text)

How the Function Works

To understand how the Combiner.CombineTextByPositions function works, let's take a look at the M code behind it.

let

CombineTextByPositions = (table as table, positions as list, optional delimiter as text) =>

let

columns = Table.ColumnNames(table),

selectedColumns = List.Select(columns, each List.Contains(positions, columns.IndexOf(_))),

combinedColumnName = “Combined”,

combinedColumn = Table.AddColumn(table, combinedColumnName, each Text.Combine(List.Transform(List.Select(selectedColumns, each not List.IsEmpty(_)), each Text.From(_)), delimiter), type text),

removedColumns = List.RemoveFirstN(List.RemoveItems(columns, selectedColumns), 1),

reorderedColumns = List.InsertRange(List.InsertRange(removedColumns, positions, selectedColumns), 0, {combinedColumnName}),

outputTable = Table.SelectColumns(combinedColumn, reorderedColumns)

in

outputTable

in

CombineTextByPositions

The function first takes the table and the list of positions as input. It then gets a list of all the column names in the table and selects only those columns that correspond to the positions specified in the list.

Next, the function creates a new column with a unique name (Combined by default) and uses the Text.Combine function to concatenate the values in the selected columns into a single text value, using the specified delimiter (comma by default).

The function then removes the original selected columns from the table and inserts the combined column in their place. Finally, it reorders the columns in the table to match the original order, and returns the resulting table.

Examples of Usage

Let's look at a few examples of how you can use the Combiner.CombineTextByPositions function in Power Query.

Example 1: Combining First and Last Names

Suppose you have a table with columns for first name, last name, and email address, and you want to combine the first and last names into a single column.

Here is the M code you can use:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlSK1YlWMjI3VIrKzSspVrISkkxMSWnKzUnNyktSgDmJ4Q==”, BinaryEncoding.Base64)), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”First Name” = _t, #”Last Name” = _t, #”Email Address” = _t]),

#”Combined First and Last Names” = Combiner.CombineTextByPositions(Source, {0, 1}),

#”Removed Email Address” = Table.SelectColumns(#”Combined First and Last Names”, {“Combined”, “Email Address”})

in

#”Removed Email Address”

Example 2: Combining Multiple Columns with Custom Delimiter

Suppose you have a table with multiple columns, and you want to combine the values in columns 2, 4, and 6 into a single column, using a custom delimiter (|).

Here is the M code you can use:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45Wqk8vyklR0lEyMjQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyNjc0MNQwVrIyVrAwN9EokxJbIzUwM9DQAA==”, BinaryEncoding.Base64)), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Column1″ = _t, #”Column2″ = _t, #”Column3″ = _t, #”Column4″ = _t, #”Column5″ = _t, #”Column6″ = _t]),

#”Combined Columns 2, 4, and 6 with Custom Delimiter” = Combiner.CombineTextByPositions(Source, {1, 3, 5}, “|”),

#”Removed Other Columns” = Table.SelectColumns(#”Combined Columns 2, 4, and 6 with Custom Delimiter”, {“Combined”})

in

#”Removed Other Columns”

The Combiner.CombineTextByPositions function is a powerful tool that allows you to combine text values from different columns in Power Query. By understanding the M code behind the function, you can customize its behavior to suit your needs and make the most of your data.

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)