Power BI CONCATENATE Function (DAX): How and When to Use it Simplified 101

Visual Analytics is the union of Data Analytics and Visualisations. This problem-solving approach is concerned with effectively facilitating high-level, complex activities such as reasoning and data-driven decision-making by integrating interactive visual representations with underlying analytical processes. Power BI is a Business Intelligence (BI) tool and a Visual Analytics engine offered by Microsoft that provides you with real-time high-level analytics, extensive modeling, and custom development. It has led the charge in making business analysis more efficient through services that are intuitive, interactive, and easy to use.

In this article, you will gain information about Power BI CONCATENATE Function. You will also gain a holistic understanding of Power BI, its key features, DAX functions in Power BI, and the usage of Power BI CONCATENATE Function. Read along to find out in-depth information about Power BI CONCATENATE Function.

What is Power BI?

Power BI is a business analytics tool developed by Microsoft that enables users to visualize data, share insights, and create interactive reports and dashboards. It integrates seamlessly with various data sources, offering robust data modeling, real-time analytics, and collaboration features, making it ideal for data-driven decision-making.

What are DAX Functions in Power BI?

Data Analysis Expressions (DAX) are a set of functions and operators that can be used to build formulas and expressions in Microsoft SQL Server Analysis Services, Excel Power Pivot, and Power BI Desktop. A DAX function is a predefined formula that performs calculations on the values passed to it as arguments.

The arguments in a function need to be in a particular order and can be a column reference, numbers, text, constants, another formula or function, or a logical value such as TRUE or FALSE. Every function performs a particular operation on the values enclosed in an argument. You can use more than one argument in a DAX formula.

Some of the Power BI DAX functions are:

• Time Intelligence Functions: These functions assist you in creating computations that make use of the built-in calendar and date knowledge. You can make meaningful sales, inventory, and other comparisons over similar time periods by combining time and date ranges with aggregations or other calculations.
• Date & Time Functions: These are used to do calculations on date and time values.
• Logical Functions: These are used to logically assess an expression or argument and return TRUE or FALSE depending on whether or not the condition is fulfilled.
• Mathematical & Trigonometric Functions: These are used to conduct a variety of mathematical operations on the values specified.

Key Points about DAX Functions

Here are some unique facts about DAX functions that you must know in order to understand them better:

• Any DAX function will always refer to an entire column/field or table. It will never make any reference to personal values. If you want to use the functions on different values in a column, you must use filters in a DAX formula.
• The flexibility of DAX functions allows you to create a formula that is applied row by row. Calculations or formulas are applied in accordance with the context of the values in each row.
• In some cases, DAX functions return an entire table that can be used in other DAX formulas that require a complete set of values. However, you are unable to view the contents of this table.
• Time intelligence functions are a subset of DAX functions. These functions are used to compute time/date ranges and periods.

What is Power BI CONCATENATE Function?

The Power BI CONCATENATE function is a DAX function that combines two text strings into one. Text, integers, or Boolean values displayed as text, or a mix of those elements can be connected. If the column has appropriate values, you may also utilize a column reference.

1) Syntax

The basic syntax of the Power BI CONCATENATE function is as follows:

CONCATENATE(<text1>, <text2>)  

2) Parameters

text1, text2: These are the text strings that will be combined to form a single text string. Strings can include both text and numbers. Column references can also be used.

3) Return value

The Power BI CONCATENATE function returns the concatenated string.

4) Remarks

• The Power BI CONCATENATE function in DAX only takes two parameters, but the Excel CONCATENATE function takes up to 255.
• If you need to concatenate several columns, you may use the nested Power BI CONCATENATE function to produce a sequence of computations, or you can use the concatenation operator (&) to unite all of them in a simpler statement.
• If you need to incorporate text strings directly (rather than utilizing column references) in the join, make sure to surround each string in a double quote mark.
• When used in calculated columns or row-level security (RLS) rules, this function is not supported in DirectQuery mode.

Use Cases of Power BI CONCATENATE Function

Several use cases of the Power BI CONCATENATE function are as follows:

1) Concatenation of Literals

By concatenating literals means that you can combine two string values that are given as arguments to the Power BI CONCATENATE function which will result in a single new string value.

The sample formula creates a new string value by combining two string values that you provide as arguments.

= CONCATENATE("Hello ", "World") 

Output:

Hello World

In the above example, a space is deliberately left after “Hello” to ensure that there is a space between the 2 arguments while concatenating them. Otherwise, if no space had been left then the output would have been “HelloWorld”.

2) Concatenation of Strings in Columns

By concatenating strings in columns means that you can concatenate strings where column names are given as arguments and the concatenation is applied on a row-by-row basis for the columns given as arguments.

The below formula returns the customer’s full name in the Phone Book database. The concatenation applied on the “LastName” and “FirstName” columns. For the first Power BI CONCATENATE function, it can be seen that the nested CONCATENATE function is given as the second argument. This is one method for concatenating multiple strings when you have more than two values to use as arguments.

= CONCATENATE(Customer[LastName], CONCATENATE(", ", Customer[FirstName]))

The above code of Power BI CONCATENATE function will help to concatenate the value present in two columns, i.e, LastName and FirstName into the resulting column along with a “,” and ” ” (space) in between.

The pattern of output will be:

 "LastName, FirstName"

In the below image, the “First Name” and “Last Name” columns are concatenated.

The code for using Power BI CONCATENATE function.

Full Names =
CONCATENATE (
    Sample_HR_Dataset_v2[First Name],
    CONCATENATE ( " ", Sample_HR_Dataset_v2[Last Name] )
)    


The output column showcases the usage of the Power BI CONCATENATE function.

If you also want to include the values of the “Middle Initial” column in the “Full Names” column, then for this additional argument, you can use the Nested POWER BI CONCATENATE function as shown below.

Full Names v2 =
CONCATENATE (
    CONCATENATE (
        Sample_HR_Dataset_v2[First Name],
        CONCATENATE ( " ", Sample_HR_Dataset_v2[Middle Initial] )
    ),
    CONCATENATE ( " ", Sample_HR_Dataset_v2[Last Name] )
)    

The resulting output column “Full Names v2” is as follows:

3) Conditional Concatenation of Strings in Columns

By Conditional Concatenation of Strings in Columns means that you can create a concatenated column based on a condition given to the Power BI CONCATENATE function.

The following formula adds a new calculated column to the Customer table that contains the full customer name as a combination of first name, middle initial, and last name. However, it also checks to see if the customer has a middle name. If there is no middle name, the last name follows immediately after the first. If there is a middle name, just the first letter is used, and the first letter is followed by a period.

= CONCATENATE( [FirstName]&" ", CONCATENATE( IF( LEN([MiddleName])>1, LEFT([MiddleName],1)&" ", ""), [LastName]))

In the above formula, nested Power BI CONCATENATE function to accommodate the combination of more than 2 strings. IF function is used to conditionally concatenate the strings. And “&” operator is used to add spaces as separators.

Suppose you want to create a field for customers with their Full Name, which is a combination of “First Name“, “Middle Initial” and “Last Name” columns. However, in the absence of a “Middle Initial,” you must add a conditional concatenation to take the initial letters of the “First Name.”

The code for creating the column by applying conditional concatenation of strings is as follows:

Cond_Full Names =
CONCATENATE (
    Sample_HR_Dataset_v2[First Name] & " ",
    CONCATENATE (
        IF (
            LEN ( Sample_HR_Dataset_v2[Middle Initial] ) < 1,
            LEFT ( Sample_HR_Dataset_v2[First Name], 1 ) & ", ",
            Sample_HR_Dataset_v2[Middle Initial] & ", "
        ),
        Sample_HR_Dataset_v2[Last Name]
    )
)   

In the above formula, nested Power BI CONCATENATE function to accommodate the combination of more than 2 strings. IF function is used to conditionally concatenate the strings. And “&” operator is used to add spaces as separators.

The resulting output column “Cond_Full Names” is as follows:

4) Concatenation of Columns with Different Data Types

By concatenation of Columns with different Data Types means that you can create a concatenated column by concatenating two columns as arguments to the Power Bi CONCATENATE function but these columns don’t have the same data type.

The following example shows how to concatenate values from columns with different data types. If the value being concatenated is numeric, it will be implicitly transformed to text. If both values are numeric, they will be converted to text and concatenated as if they were strings.

Product description	Product abbreviation (column 1 of composite key)	Product number (column 2 of composite key)	New generated key column
Mountain bike	MTN	40	MTN40
Mountain bike	MTN	42	MTN42

The formula for concatenating the columns as showcased in the above example:

= CONCATENATE('Products'[Product abbreviation],'Products'[Product number])  

The Power BI CONCATENATE function in DAX only takes two arguments, but the Excel CONCATENATE function takes up to 255. You may use the ampersand (&) operator to add extra arguments.

For example, the given formula also produces the same output, MTN-40 and MTN-42.

= [Product abbreviation] & "-" & [Product number]  

This is a prominent use for the Power BI CONCATENATE function. It might be a concatenation of Literals and Strings in Columns or any other form. Typical use cases include when you need to construct conditional dynamic visual headers or titles, or simply join columns with multiple data types to generate a unique column for creating connections in Power BI data modeling using bridge tables.

For further information on Power BI CONCATENATE function in DAX, you can visit here.

Tips for Efficient Data Concatenation in Power BI

• Validate Your Formulas: Regularly test formulas to catch errors or inconsistencies early.
• Verify Column Names: Ensure column names are spelled correctly and match the case.
• Leverage CONCATENATEX for Mixed Data Types: Use this function to handle varied data types effectively.
• Focus on Performance: Avoid overly complex concatenation formulas in large datasets to keep performance optimized.

Conclusion

In this article, you have learned about Power BI CONCATENATE Function. This article also provided information on Power BI, its key features, DAX functions in Power BI, and the usage of Power BI CONCATENATE Function.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Hevo Data with its strong integration with 150+ Data Sources (including 60+ Free Sources) such as Power BI allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. Sign up for Hevo’s 14-day free trial so that you can forget about data migration struggles, focus on your key business needs and perform insightful analysis using BI tools. 

FAQ on Power BI CONCATENATE Function (DAX)

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the Power BI CONCATENATE Function in the comment section below! We would love to hear your thoughts.