Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

In this article, you’ll learn how to concatenate values in Power Query. A common data transformation operation is to combine values from different columns or even across rows. This can be tricky depending on data types or specific values. This guide explores various methods for concatenating values in Power Query.

If you prefer a live demonstration, check out this video on concatenation:

Concatenate values in Power Query M | Avoid these pitfalls!
Table of contents

What is concatenation and why is it useful?

What is concatenation? Concatenation is an operation where you combine two or more values into a single string. This is a common operation when preparing data, you will often see it for combining:

  • Names: Combine first and last name for a Full Name
  • URLs: Combine Protocol, Domain and Path into a single URL
  • Addresses: Combine Address, Zip Code and City into a Full Address
  • Categories: Combine category and Description into a Full Product Description

Let’s dive into the methods for concatenating values in Power Query.

Concatenate Values

When working with Power Query, it’s important to know how to concatenate values. There are a few methods available in the M language allow you to concatenate.

Combination Operator: &

One of the simplest ways to concatenate in Power Query is to use the Combination Operator, represented by the ampersand symbol.

Let’s say you have two columns in your table, “First Name” and “Last Name”. You can use Power Query’s Combination Operator to combine texts into a single “Full Name”.

To concatenate two columns in Power Query, you:

  • Write [First Name] & ” ” & [Last Name].
  • The ampersand (&) combines the column values.
  • The space between Double Quotes adds a space between the columns.

After these steps, Power Query combines the text values into a single string.

[First Name] & " " & [Last Name]
Concatenate using the Combine Operator in Power Query

Using Text.Combine

Another way to concatenate values in Power Query is by using the Text.Combine function. This function can combine text values from a list with an optional separator between them.

Imagine you have a table with columns for “Address”, “City”, and “Zip Code”. If you want to create a new column with the full address, you can use the Text.Combine function.

The easiest way to concatenate strings in Power Query is:

  • Start the Text.Combine function
  • Provide the list of text values, separated by commas
  • Close the function

Tip

To indicate the opening and closing of a list, you can enclose them between curly brackets, like: { "a", "b", "c" }.

You end up with a statement like this:

Text.Combine(
  { [Address],  ", ", 
    [City],     ", ", 
    [Zip Code]
  } )
Concatenate using Text.Combine in Power Query

As you can see, the before example manually provides commas to separate values. You can also use other functions to concatenate text values with a custom delimiter, such as a hyphen or a semicolon. More on that in the next section.

Using Merge Columns

The no-code way to concatenate multiple columns is by using the Merge Columns feature in the ribbon.

The fastest way to concatenate columns with the User Interface is to:

  • Hold CTRL + select the columns to Merge
  • Go to the Add Column Tab
  • Click on Merge Columns
  • Optionally enter a separator and a New Column Name
  • Press OK
Merge Columns in Power Query

You now have a new column with the strings concatenated. Power Query automatically generates the code to combine the columns automatically. The code is the same as the Text.Combine method described before.

Concatenate With Delimiters

You might find yourself needing to concatenate column values with a delimiter, such as a comma, space or a hyphen. Power Query has two easy functions to concatenate with a delimiter: Text.Combine and List.Accumulate.

Using Text.Combine

The Text.Combine function allows you to concatenate text values from a list with a specified delimiter. That makes it easy to concatenate multiple columns. To use this function, simply input a separator as text in the second argument.

Here are two examples that give the same result:

Text.Combine( 
    { [Address], ", ", 
      [City],    ", ", 
      [Zip Code] 
    } )

Text.Combine( 
    { [Address], [City], [Zip Code] },
    ", " )                             // specifies delimiter
Text.Combine Delimiter Argument in Power Query

While manually inputting delimiters is easy for just a few values, it can quickly become tedious when working with larger lists. That’s where the delimiter argument really shines.

Using List.Accumulate

The List.Accumulate function is another way to concatenate text values with a custom separator. This function takes a list and an accumulator function as input. This allows you to instruct the function to concatenate strings with a delimiter.

For example, the following formula would concatenate the values in a list with a comma separator:

List.Accumulate(
  {[Address], [City], [Zip Code]}, 
  "", 
  (state, current) => 
    if state = "" then current 
       else state & ", " & current
)

You can also make use of the Coalesce Operator in M to handle the first iteration when concatenation might result in a null value:

List.Accumulate(
  {[Address], [City], [Zip Code]}, 
  null, 
  (state, current) => (state & ", " & current) ?? current
)

By using Text.Combine and List.Accumulate functions, you can easily concatenate your text values with delimiters. For more information, you can read this article that explains List.Accumulate with easy to understand examples.

Mixing Data Types

Besides concatenating text values, you may need to concatenate values of different data types, such as numbers and text, or dates and numbers. Yet, mixing data types can bring some challenges in Power Query.

Problems with Mixing Data Types

When combining different data types, you are likely to run into an error. For example, the Combination Operator won’t work if you want to concatenate a number with a text value. The Combination Operator (&) only works with Text Values, Lists and Records.

When trying to combine these, Power Query returns the error: “Expression.Error: We Cannot apply operator & to types Text and Number“.

Expression Error when Mixing Data Types in Power Query

So how can you combine text and number in M without any errors? To prevent errors from happening, you need to transform both data types to the same type, such as text. You can do this by either:

  1. changing the data type of the entire column .
  2. using a function that transforms the data type during the evaluation.

To transform a data type into text, you can use one of the following functions:

The below examples illustrate how you can use some of these functions to prevent the mixing of data types.

Concatenating Text

Concatenating text values with other text values is easy. You can simply use the Combine Operator (&) or the Text.Combine function as described earlier in this article.

Concatenating Numbers and Text

To concatenate a number type with a text type value in the M language, you can convert the number to text using the Text.From function. For example, if you have a column for “Month” and a column for “Year”, you can use the following formula to concatenate them into a single string:

[Month] & " " & Text.From( [Year] )
Combine Text and Numbers in Power Query

Concatenating Dates and Text

If you need to concatenate date type with a text type values, you can convert the date to text using the Text.From or Date.ToText function. For example, if you have a column for “Date” and a column for “Event”, you can use the following formula to concatenate them into a single string:

[Event] & " on: " & Text.From( [Date] )

Alternatively, if you’re interested in returning a specific text format for the date, you can also use the Date.ToText function in combination with a custom formatting string.

[Event] 
& " on: " 
& Date.ToText( [Date], [Format = "d MMMM yyyy"] )
Concatenate Text and Dates

Tip

Read this article for more details on custom formatting strings.

Handling Null Values When Concatenating

When you’re mixing column types, null values may give you some trouble. For example, you want to create a column that states the ID and the Product Description of products. Yet some products don’t yet have an ID and show null instead.

A first try to concatenate values could be:

"Code: " 
& Text.From( [Id] ) 
& ": " 
& [Description] 

However, in case the Id column (a number data type) contains null values, the concatenation results in null.

Concatenate Text and Null Values in Power Query

This is not always the desired result. So, what can you do to concatenate columns with null values? By using the Text.Combine function instead, you can prevent the result from showing null. This function ignores null values and continues the concatenation.

Text.Combine(
   { "Code: " 
     ,Text.From( [Id] ) 
     ,": " 
     ,[Description] 
   } )
Use Text.Combine to Concatenate Null Values in Power Query

And with that change, the code concatenates all values that are not null.

Note

Whereas this approach ignores null values, the delimiters are still part of the concatenation. If you don’t want this, you can instead concatenate with a condition.

Concatenate Values with Conditions

There are times when you need to concatenate values based on certain conditions. For example, you may want to exclude null values when concatenating values. To do this in Power Query, you can use an if statement to control the concatenation logic.

Tip

If statements are conditional expressions, they let you perform different operations depending on whether a condition is true or false.

To exclude null values in a concatenation, you can use an if statement to check if a certain column contains a null value. If it does, you can concatenate only the non-null values.

Here’s an example:

if [Id] = null then [Description] 
    else Text.Combine(
           { "Code: " 
             ,Text.From( [Id] ) 
             ,": " 
             ,[Description] 
           } )

In this example, the if statement checks if the column [Id] contains a null value. If it does, only the [Description] column is returned. If it doesn’t, the [Id] column is also concatenated using the Text.Combine function.

Concatenate Values and Ignore Null in Power Query

By using if statements, you ensure that your final output only includes the values that meet your specific requirements.

Concatenate Values without Duplicates

Let’s talk about how to merge values in Power Query without getting any duplicates. Sometimes, you only need unique values in your output, so you don’t get any clutter.

To do this, you can combine some functions that we’ve already discussed. Remember the Text.Combine function? We used it earlier with a delimiter. Now, let’s say you have a list of values that includes some duplicates:

{ "Mug", "Vase", "Vase", "Rug", "Desk", "Book", "Book" }

To merge this list of values without any duplicates, you can use the Text.Combine function together with List.Distinct. Here’s how it looks:

Text.Combine(
      List.Distinct( { "Vase", "Vase", "Rug", "Desk", "Book", "Book" } ), 
      ", " )

// Returns: { "Vase", "Rug", "Desk", "Book" }

That’s it! With just a few functions, you can merge columns in Power Query without any duplicates. This will help keep your output clean and clutter-free.

Concatenate Values Across Multiple Rows

Sometimes, you may want to concatenate values from multiple rows into a single row. This can be useful when you want to summarize or aggregate data.

In Power Query, you can use the Group By feature to group data by one or more columns and then apply an aggregation function to the grouped data. One of the most common aggregation functions used for concatenating rows is the Text.Combine function.

To concatenate values across multiple rows, first, group the data by the desired column using the Group By feature. Then, adjust the M-code to include the Text.Combine function for concatenating the values in each group.

Tip

For more details you can read this article on concatenating text values using Group By in Power Query.

Conclusion

In conclusion, concatenating values in Power Query can help you combine multiple text values into a single string. The Combination Operator and Text.Combine function are the most commonly used methods for that. Just be careful when mixing data types and including null values.

Lastly, the Group By feature in Power Query lets you concatenate values across multiple rows. By mastering these techniques, you can shape your data in the desired form.

Happy Querying!

Share this post:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.