How to Concatenate Your Data: The Power BI Way

by

While working with Power BI, you might have observed that the data you load is not in the adequate form you wanted it to be in. Often the data is stored in different columns that you want to combine together.

The method of combining rows or columns in Power BI is nothing but concatenation. It is a part of the Transform Data step in the tool.

Suppose you are working on the employee’s database, and the First Name, Last Name, and Address details are placed into different columns. You wish to combine either First and Last Names, or Street, City, Province, Pin Code details together as an Address. You are also expecting to concatenate the data based on a particular condition. That is also possible in Power BI.

Throughout this article, you will learn different methods that work for you to combine two or more rows or columns together in Power BI, including the DAX formulae. If that fascinates you, keep reading until the last line, and I will make sure you learn at least six or seven ways to get this task done in Power BI.

Let’s play with data and CONCATENATE together! 😉

The Dataset

You will have the Employee Details dataset for this demo, as shown above. It consists of 8 columns and 20 rows. You may want to combine the First and Last Name or the Street, City, County, State, and Pin Code together.

You are free to play with this data. But you first have to download the same. To do that, click on the Download button below to get the Excel database as well as the Power BI file. The model is already loaded into the Power BI file for you.

Download Now

Concatenate Using the Merge Columns Option

Suppose you wish to combine the First Name and the Last Name columns together to generate the Full Name. You can use the Merge Columns option through Power Query to combine them. The results will be stored in a new merged column that will replace the original columns available by default. However, you can add a name of your choice to the merged column. Moreover, you can also create a custom merged column to keep the First Name and Last Name columns for further references.

Navigate to the Home menu and click on the Transform Data dropdown through the Queries section. Click on the Transform Data option to access the Power Query Editor out of all the available options.

  1. In the Power Query Editor, select the columns you want to merge together, i.e., the First Name and Last Name in this case.
  2. Click on the Transform menu to access all the available transformation options available in it.
  3. Inside the Text Column section, you will see the Merge Columns option. Click on it.

In the Merge Columns window that opens up, select Space as a Separator through the dropdown and change the column name to Full Name through the New column name (optional) text box. Finally, click the OK button to implement these changes.

Once you hit the OK button inside the Merge Columns window, the system will replace the original First Name and Last Name columns with a new column to show you the combined output as above.

Concatenate Using the Text.Combine Function

Those who are familiar with the M Query language will prefer to use the Text.Combine function to concatenate two fields together. The function is a part of the M Query Text Functions group. The function contains a list of text values as an input and combines them with a separator.

  • In Power Query Editor, click on the Add Column tab. It has multiple options to add custom columns.
  • Through the general section, click on the Custom Column menu to add a custom formulated column in your query.

Into the Custom Column window that opens up,

  • Add the column name as Full Name through the New column name text box.
  • Under the Custom column formula section, use the Text.Combine function to concatenate First Name and Last Name. The formula is as shown below. Feel free to use it by copying the same.
Text.Combine({[First Name], [Last Name]}, " ")
  • Click on the OK button to execute the function.

As soon as you hit the OK button, a new column will be added at the end of the original table with a title as Full Name containing First and Last Name separated with a space.

Pro Tip: You can also select the column names through the Available Columns section by clicking on a column you want to add, then hit the Insert button placed at the bottom. This way, your formula will be free from typing errors.

Note: In the Text.Combine function, you must have seen the column names placed within curly braces (‘{}’). They convert the column inputs into the list as the function demands list input arguments for the concatenation. Make sure you use that when you want to combine text columns.

Concatenate Using the Ampersand Operator

Using the ampersand (‘&’) to combine two columns is a common practice in most of the tools, and Power BI is no different from those. It simply works as a concatenation operator to combine several strings or columns containing strings together.

Similar to the previous example, navigate towards the Add Column menu and click on the Custom Column option through the General section to add a formulated column inside the data model.

  • Firstly, change the custom column name as Full Name through the New column name section. It appears into the Custom Column window that opens up as soon as you click the menu with the same name.
  • You then use the Custom column formula section to develop a formula that concatenates two columns using the ampersand operator. Feel free to use the formula as shown below:
=[First Name]&" "&[Last Name]

Here, the columns First Name and Last Name are concatenated with each other in addition with the Space as a separator between both of them.

Note the placement of the ampersand. It is used after the First Name to concatenate it with the Space. Then, it is used to concatenate First Name and Space with the Last Name in the second part of the formula.

  • Finally, click on the OK button to execute this formula. It adds the Full Name column at the end of the table.

The result will be as shown in the screenshot above, where names are concatenated with Space as a delimiter.

Using the CONCATENATE Function

Despite the methods discussed above, a specific function is dedicated to the task and named against the same. The CONCATENATE function!

It takes two strings and then combines them without any separator. You can also make it work on columns containing text strings or columns with Number, Boolean, or Date type stored as text.

The syntax for the CONCATENATE function is as shown below:

=CONCATENATE(Text1, Text2)

Where,

Text1 and Text2 are two text strings you want to concatenate into a single text string.

Let’s use it in the Power BI Data view to concatenate First and Last Name.

  • In Power BI report, go to the Data view from the left pane.
  • Navigate towards the Table tools menu and click on the New Column option through the Calculations section.

It allows you to create a new calculated column using the DAX formula that evaluates for each row within the given data table.

Inside the formula pane, change the column name as Full Name (system by default adds a one with name Column).

Then, use the CONCATENATE function at the right-hand side of equals to. It will concatenate two columns. Use the First Name and Last Name columns inside the function from the Emp Data table. The formula is as shown below:

Full Name = 
CONCATENATE(
    'Emp Data'[First Name],
    'Emp Data'[Last Name]
)

Pay close attention to the way of specifying the column names. In DAX formulas, you must specify the column arguments as ‘Table_Name'[Column_Name].

Once you execute this function, the column Full Name looks like the one shown above.

You can see an immediate discrepancy here! There is no space between the First and Last Names because the function takes two arguments and doesn’t deliberately separate them with a delimiter.

Let’s modify this Full Name column by nesting one more CONCATENATE function inside the first one. It will hold the delimiter and Last Name together to combine them with the First Name. The formula is as shown below:

Full Name = 
CONCATENATE(
    'Emp Data'[First Name],
    CONCATENATE(" ",'Emp Data'[Last Name])
)

Here, the second CONCATENATE combines the Space and Last Name together. Then, it is used as a second argument under the first CONCATENATE function that holds the First Name.

Let’s have a look at the output now in the Full Name column. It now looks better with the Space as a delimiter between combined First and Last Names.

Concatente Using the COMBINEVALUE Function

The COMBINEVALUE function was developed with the primary purpose of managing the multi-column relationship models into the DirectQuery models. However, most of the users use it to concatenate two or more text strings together into a single text string.

The syntax for the COMBINEVALUE function is as shown below:

COMBINEVALUES(<delimiter>, <expression>, <expression>[, <expression>]…)

Where,

delimiter – is a mandatory argument that specifies the separator used to separate two strings.

expression – a mandatory argument that specifies a DAX expression after evaluating which values must be combined. It can be replaced with a column name from the table, though. You will need at least two expressions to evaluate this function.

As mentioned at the start, this function helps concatenate more than two text strings into a single. This way, it is a notch higher than the CONCATENATE function, which only accepts two expressions.

Now, using the same New column menu from the Calculations section, add a column that can combine First and Last Names. The formula is as shown below, and the results can be seen in the screenshot above.

Full Name = 
COMBINEVALUES(
        " ",
        'Emp Data'[First Name],
        'Emp Data'[Last Name])

Conditional Concatenation

For data analysts, a situation may appear when they want to concatenate the strings based on a condition that holds true. Power BI allows you to use the CONCATENATE or COMBINEVALUE function in combination with the conditional IF statement to get it done.

Suppose you want to know the full name of employees that have salaries greater than or equals to $20,000 to ping them and submit the Income Tax proofs towards the HR department as the end of financial year is approaching.

You can easily club IF and functions that can concatenate the First Name and Last Name columns.

In the example we shared above, the COMBINEVALUE function is clubbed with the conditional IF statement to generate the Full Names for those employees whose salaries are greater than or equals to $20,000. The formula that works here is as shown below.

Full Name_cond = 
IF(
    'Emp Data'[Salary] >= 20000,
    COMBINEVALUES(
        " ",
        'Emp Data'[First Name],
        'Emp Data'[Last Name]
    )
    ,
    ""
)

How to CONCATENATE More than TWO columns

The last section is where we discuss the CONCATENATION of more than two text values. In the example, we are using for this article, you have Street, City, County, State, and Pin Code details that combined together form the address of an employee that you need to send an annual Christmas gift to their home.

You can do this using either the standard concatenate operator (i.e. ampersand), or using the series of nested CONCATENATE functions or a straightforward COMBINEVALUE function.

Irrespective of using any of the three formulae mentioned below, you are going to get the same result as shown above.

Address = 
'Emp Data'[Street]
&", "&
'Emp Data'[City]
&", "&
'Emp Data'[County]
&", "&
'Emp Data'[State]
&", "&
'Emp Data'[Pin Code]

The ampersand operator combines the different columns and in between each column, there is a delimiter comma and space.

Address = 
CONCATENATE(
    'Emp Data'[Street],
    CONCATENATE(
        ", ", 
        CONCATENATE(
            'Emp Data'[City],
            CONCATENATE(
                ", ",
                CONCATENATE(
                    'Emp Data'[County],
                    CONCATENATE(
                        ", ",
                        CONCATENATE(
                            'Emp Data'[State],
                            CONCATENATE(
                                ", ",
                                'Emp Data'[Pin Code]
                            )
                        )
                    )
                )
            )
        )
    )
)

The CONCATENATE function only combines two values and hence it is a bit tedious to write a formula that does the task as well as it looks shabby to read and understand.

Address = 
COMBINEVALUES(
    ", ",
    'Emp Data'[Street],
    'Emp Data'[City],
    'Emp Data'[County],
    'Emp Data'[State],
    'Emp Data'[Pin Code]
)

However, the working of the COMBINEVALUE function is crisp in comparison with the other two and it is also simple to understand. You need to specify the delimiter only once and then all the columns you want to combine.

Note: All three functions provide you the capability to concatenate Pin Code, a column of numeric values with the other text valued columns.

Conclusion

Throughout this article, you have been introduced to five standard methods to concatenate values in Power BI. Besides those, you also know how to combine more than two columns and how to concatenate values based on a condition.

  • The Merge Columns option is one of the easiest ways to concatenate. It takes two column names and a delimiter to separate them in combined result.
  • Those who follow the standard M Query syntaxes has the Text.Combine() function that allows you to concatenate two columns or values based on a delimiter.
  • The ampersand operator is first step to concatenate two or more columns in Power BI DAX environment and allows you to combine columns with delimiters all togeher as the operator itself suggests.
  • The CONCATENATE function is powerful to combine two values but have it’s own limitations when it comes to combine more than two columns altogether. The function fails in such situations and you have to use the nested CONCATENATE in case you want to combine more than two values.
  • The COMBINEVALUE is the best of all to concatenate two or multiple columns or values together. It takes all the values as expressions and delimiter to separate those in concatenated result.

Apart from these five methods, you also learn how to concatenate values when a specific condition based on another column meets. You use the conditional IF with an ampersand, CONCATENATE, or COMBINEVALUE to get it done.

The article ends here and I wish it solves almost every problem of concatenation in your day-to-day work life in Power BI. If it does, do follow our page for more such stuff and let me know in the comments section about anything I am missing.

Leave a Comment