Combiner.CombineTextByEachDelimiter

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will delve into the M code behind this powerful function, explore its capabilities, and provide some practical examples of how it can be used to manipulate data.

Overview of the Combiner.CombineTextByEachDelimiter Function

The Combiner.CombineTextByEachDelimiter function is a Power Query function that allows you to combine text values in a column, separated by one or more delimiters of your choice. This function is particularly useful when you have data that is not formatted in a way that makes it easy to analyze.

The syntax for the Combiner.CombineTextByEachDelimiter function is as follows:

Combiner.CombineTextByEachDelimiter(delimiters as list, optional quoteStyle as nullable number) as function

The `delimiters` parameter is a list of one or more characters that you want to use as delimiters to separate the text values in the column. The `quoteStyle` parameter is optional and allows you to specify the type of quotes to use when combining the text values.

Here is an example of how you can use the Combiner.CombineTextByEachDelimiter function to combine text values in a column separated by a comma:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t])),

CombinedText = Table.AddColumn(Source, “Combined Text”, each Combiner.CombineTextByEachDelimiter({“,”})(List.RemoveNulls({[Column1], [Column2]})))

in

CombinedText

Understanding the M Code

Let's take a closer look at the M code behind the Combiner.CombineTextByEachDelimiter function.

First, we create a table called `Source` using the `Table.FromRows` function. The `Source` table contains two columns called `Column1` and `Column2`.

Next, we create a new column in the `Source` table called `Combined Text` using the `Table.AddColumn` function. The `each` keyword is used to specify that we want to apply a function to each row in the `Source` table.

Inside the `each` function, we call the `Combiner.CombineTextByEachDelimiter` function and pass it a list of delimiters (`{","}`) that we want to use to separate the text values in the `Column1` and `Column2` columns.

Finally, we use the `List.RemoveNulls` function to remove any null values from the list of text values before passing it to the `Combiner.CombineTextByEachDelimiter` function. This ensures that we only combine non-null text values.

Practical Examples

Now that we understand the M code behind the Combiner.CombineTextByEachDelimiter function, let's explore some practical examples of how it can be used.

Example 1: Combining Text Values Separated by a Delimiter

Suppose you have a table with two columns called `FirstName` and `LastName`, and you want to combine the values in these columns into a single column separated by a space.

You can use the following M code to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t])),

CombinedText = Table.AddColumn(Source, “Full Name”, each Combiner.CombineTextByEachDelimiter({” “})(List.RemoveNulls({[FirstName], [LastName]})))

in

CombinedText

Example 2: Combining Text Values from Multiple Columns

Suppose you have a table with three columns called `City`, `State`, and `ZipCode`, and you want to combine the values in these columns into a single column separated by a comma.

You can use the following M code to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, State = _t, ZipCode = _t])),

CombinedText = Table.AddColumn(Source, “Address”, each Combiner.CombineTextByEachDelimiter({“, “})(List.RemoveNulls({[City], [State], [ZipCode]})))

in

CombinedText

Example 3: Combining Text Values with Quotes

Suppose you have a table with three columns called `FirstName`, `LastName`, and `EmailAddress`, and you want to combine the values in these columns into a single column separated by a comma and enclosed in quotes.

You can use the following M code to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“…”, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, EmailAddress = _t])),

CombinedText = Table.AddColumn(Source, “Contact Info”, each Combiner.CombineTextByEachDelimiter({“, “}, QuoteStyle.Csv)(List.RemoveNulls({[FirstName], [LastName], [EmailAddress]})))

in

CombinedText

The Combiner.CombineTextByEachDelimiter function is a powerful tool in Power Query that allows you to combine text values in a column separated by one or more delimiters of your choice. By understanding the M code behind this function, you can create custom transformations that will help you clean and reshape your data in new and interesting ways.

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)