Combiner.CombineTextByDelimiter

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Combiner.CombineTextByDelimiter Function

The Combiner.CombineTextByDelimiter function is used to combine multiple columns of text into a single column separated by a delimiter. The function takes three arguments: the delimiter, the columns to combine, and an optional parameter to specify the output column name.

Here is an example of how the function works:

= Table.AddColumn(#”PreviousStep”, “Combined”, each Combiner.CombineTextByDelimiter(“, “, {“Column1”, “Column2”, “Column3”}))

In this example, we are adding a new column called "Combined" to a table called "PreviousStep". The new column will combine the values from columns "Column1", "Column2", and "Column3" separated by a comma and a space.

The M Code Behind Combiner.CombineTextByDelimiter

The M code behind the Combiner.CombineTextByDelimiter function is straightforward and easy to understand. Here is the M code for the function:

let

CombineTextByDelimiter = (delimiter as text, columns as list, optional separator as text) as text =>

let

separator = if separator = null then “” else separator,

columnValues = List.Transform(columns, each Text.From(_)),

result = Text.Combine(columnValues, delimiter & separator)

in

result

in

CombineTextByDelimiter

The code starts with a function called "CombineTextByDelimiter" that takes three parameters: delimiter, columns, and optional separator. The function then checks if the separator parameter is null, and if so, sets it to an empty string.

Next, the function uses the List.Transform function to convert each column in the columns parameter to a text value. This is necessary because the Combiner.CombineTextByDelimiter function can only combine text values.

Finally, the function uses the Text.Combine function to combine the text values from the columns parameter using the delimiter and separator parameters.

Using Combiner.CombineTextByDelimiter for Data Cleaning

The Combiner.CombineTextByDelimiter function can be used for a variety of data cleaning tasks. Here are some examples:

Combining First and Last Names

Suppose you have a table with separate columns for first and last names, and you want to combine them into a single column. You can use the Combiner.CombineTextByDelimiter function to do this:

= Table.AddColumn(#”PreviousStep”, “Full Name”, each Combiner.CombineTextByDelimiter(” “, {“First Name”, “Last Name”}))

In this example, we are adding a new column called "Full Name" to a table called "PreviousStep". The new column will combine the values from columns "First Name" and "Last Name" separated by a space.

Removing Duplicates in a Column

Suppose you have a column that contains duplicate values, and you want to remove the duplicates. You can use the Combiner.CombineTextByDelimiter function to do this:

= Table.AddColumn(#”PreviousStep”, “Unique Column”, each List.Distinct(Text.Split([Column], “,”)))

In this example, we are adding a new column called "Unique Column" to a table called "PreviousStep". The new column will split the values from the "Column" column using a comma delimiter and then remove the duplicates.

Parsing Data from a Single Column

Suppose you have a column that contains multiple values separated by a delimiter, and you want to parse the values into separate columns. You can use the Combiner.CombineTextByDelimiter function to do this:

= Table.SplitColumn(#”PreviousStep”, “Column”, Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv), {“Value1”, “Value2”, “Value3”})

In this example, we are using the Table.SplitColumn function to split the values from the "Column" column using a comma delimiter and create three new columns called "Value1", "Value2", and "Value3".

The Combiner.CombineTextByDelimiter function is a powerful tool for data cleaning and transformation in Power Query. By understanding the M code behind the function, users can create custom functions to automate their data cleaning processes and save time. Whether you are combining columns of text, removing duplicates, or parsing data, the Combiner.CombineTextByDelimiter function is a valuable tool for any data analyst.

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)