What is the Power BI Concatenate function?
The Power BI CONCATENATE is a text function that enables you to combine two text strings into a single text string. Using this function, you can combine texts, numbers, Boolean values, or a combination of those items. It is also possible to use a column reference, provided the column contains related values.
There are multiple benefits you can realize using the Power BI concatenate function, including:
- Easy data transformation and manipulation.
- Help you with data formatting and presentation.
- Simple to use
- Create more meaningful labels and categories.
Table of contents
Key Takeaways
- The Power BI CONCATENATE function allows to concatenation of two text strings into a single text string
- You can concatenate texts, numbers, Boolean values, or a combination of those items using this function.
- Power BI Concatenate functions accept 2 input parameters.
- You can use the Power BI concatenate function for conditional concatenation of strings.
- Use the & or double quotes operator to apply customized formatting to your concatenated string in Power BI.
- Consider the limitations of the Power BI concatenate function for effective usage of CONCATENATE function.
Syntax
The syntax for the Power BI Concatenate function is outlined below:
CONCATENATE(<text1>, <text2>)
It accepts two parameters that include:
| Parameter Name | Mandatory/Optional | Description |
|---|---|---|
| Text1 | Mandatory | The first text string is to be joined into a single text string. The string can include text or numbers. You can also use column references. |
| Text2 | Mandatory | The second text string is to be joined into a single text string. The string can include text or numbers. You can also use column references. |
For example, the below DAX creates a new string by concatenating two different texts:
CONCATENATE(“Sample “, “Text”). The output will be SampleText.
How to use the Power BI Concatenate function?
To use Power BI concatenate, follow the steps highlighted below:
Step 1: Launch Power BI Desktop in your system.
Step 2: Import the dataset into Power BI using the Get data option and load it into Power BI using the Load button.
Step 3: Navigate to the Modeling tab and click on the New column option.
You can also use the New measure option for concatenation.
Step 4: Write the DAX expression to create a concatenated string using the Concatenate function in the formula bar. Save the changes by clicking on Enter.
ConcatenateDemo = CONCATENATE(financials[Month Name], financials[Year])
Step 5: Navigate to Table view to view the newly created column in Power BI.
Use the concatenated column for your reporting requirements in Power BI. You can perform Power BI concatenate two columns, 3 columns, and with space using the CONCATENATE function.
Examples
In this section, we will go through a few examples demonstrating the use of the Concatenate function in Power BI.
Example #1 – Concatenation of Columns with Different Data Types
In this example, we will demonstrate the function to concatenate two columns in Power BI with different data types using the Product Segment dataset. The Product Segment dataset contains the product segment details along with the abbreviation code and SKU code.
To use the Power BI Concatenation function with different data types, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.
Step 2: Right-click on the table and select the New column option.
Step 3: Write the DAX expression using CONCATENATE to Power BI concatenate two columns with different data types.
Here, we are creating a concatenated column of Product Abbreviation code and SKU code.
Concatenated Column with different data types = CONCATENATE(‘Example 1′[Product Abbreviation Code],’Example 1′[SKU Code])
Click on the Commit icon to save the changes.
Step 4: Navigate to Table view to view the newly created concatenated column.
Step 5: Navigate to the Visualizations pane and choose the Table visual icon. You will see the Table visual in the report canvas.
You will notice the newly created column is created using Power BI concatenate two columns.
Example #2 – Concatenate Text and Numbers in Power BI
We will use this example to demonstrate the function to concatenate columns in Power BI comprising text and numbers using the Employee_Data dataset. The Employee_Data dataset contains the employee details along with their business unit, division, supervisor, and other related details.
Follow the instructions outlined below to concatenate text and numbers:
Step 1: Open Power BI Desktop, navigate to Home – Get data, and click on the Load button to load the data into Power BI.
Step 2: Navigate to the Modeling tab. Choose the New column option.
Step 3: Create a Power BI concatenated column by writing a DAX expression in the formula bar using the CONCATENATE function.
Here, we are creating a concatenated column comprising EmpID and BusinessUnit columns.
Concatenation with Text and Number = CONCATENATE(‘Example 2′[EmpID],’Example 2′[BusinessUnit])
Click on the Commit icon to save the changes.
Step 4: To view the newly created concatenated column, navigate to Table view. The newly created column is highlighted in red.
Use the concatenated column for your reports or other transformations.
Example #3 – Concatenate Strings in Columns
This example primarily focuses on demonstrating the use of Power BI concatenate with space using the strings in columns using the U.S. Major League Soccer Salaries dataset. The U.S. Major League Soccer Salaries dataset contains the salaries of every MLS player from 2007 to 2017.
You can follow the steps highlighted below to use the Power BI Concatenate function for string concatenation:
Step 1: Choose the dataset from the Navigator screen. Load the dataset into the Power BI data model by clicking on the Load button.
Step 2: Once the dataset is loaded, navigate to the Modeling tab and choose the New column option.
Step 3: In the formula bar, specify the DAX expression using CONCATENATE to concatenate two strings in columns in the dataset.
If you observe here, we have specified Power BI concatenate with space to concatenate first_name and last_name columns from the dataset.
Concatenated Name = CONCATENATE(‘Example 3′[first_name],CONCATENATE(” “,’Example 3′[last_name]))
To save the changes, click on the Commit icon. It will create the new column Concatenated Name in the dataset.
Step 4: Navigate to the Visualizations pane and choose the Table visual icon.
From the Data pane, drag and drop the data fields that also include the concatenated column and map them to the report canvas.
Apply formatting to your visual if needed. It will create a Table visual, as shown below:
Example #4 – Conditional Concatenation of Strings in Columns
Now, we will demonstrate the conditional concatenation of strings in columns using the Business Unit dataset. The Business Unit dataset contains the resource details along with their associated departments.
We have outlined the steps involved in creating the conditional concatenation of strings in columns below:
Step 1: Open Power BI Desktop and select the dataset in the Navigator. Load the dataset into the Power BI data model.
Step 2: Create a concatenated column by navigating to the Modeling tab and selecting the New column option.
Step 3: Specify the logic using the DAX expression to apply conditional concatenation of strings in columns in the dataset.
In this example, we have created a conditional concatenation using Power BI concatenate 3 columns. We are adding the middle name only if it’s specified in the dataset. Otherwise, Power BI will concatenate the first name and last name columns.
Conditional Concatenation = CONCATENATE(‘Example 4′[First Name]&” “,CONCATENATE(IF(LEN(‘Example 4′[Middle Name])>1, LEFT(‘Example 4′[Middle Name],1)&” “,” “),’Example 4′[Last Name]))
Either hit the Enter button or click on the Commit icon to save the above changes.
Step 4: Now, create a data visualization by navigating to the Visualizations pane and choosing the Table visual icon. You can drag and drop the data fields from the Data pane and map them to the visual section. This will create a Table visual in the report canvas with the mapped columns as shown.
Important Things to Note
- You can specify only two parameters while using the Power BI Concatenate function in a DAX as opposed to the Excel Concatenate function that supports up to 255 arguments.
- To concatenate multiple columns, you can either create a series of calculations or consider using the concatenate operator (&) to combine the columns using a simple expression.
- To use the text strings directly, you must enclose each string within double quotes.
- Always validate the null or blank values appropriately to eliminate any erroneous results.
- Consider specifying a standard naming convention to create a concatenated column for clarity.
- For large datasets, evaluate the performance implications before using this function in your transformation logic.
- This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Frequently Asked Questions (FAQs)
Yes, you can concatenate columns from different tables in Power BI. To do this, you can follow the options provided below.
• Using Power Query:
◦ Navigate to the Power Query Editor tool.
◦ Choose the Merge Queries option in the Combine pane under the Home tab.
◦ Ensure that you keep only the required columns while discarding the other columns.
◦ This option can be useful for combining two tables to perform data analysis.
• Using DAX
◦ Use DAX to create a calculated column or measure containing the concatenated values.
◦ You can use functions such as CONCATENATE, CONCATENATEX to combine the different columns from different tables.
The key difference between using the & operator and the CONCATENATE function in Power BI include:
• & Operator: This is a symbol used in DAX to combine strings. It’s concise, has simple syntax, and is easy to use. It directly appends the strings without offering any flexibility to apply customized formatting.
• CONCATENATE Function: This is a text DAX function that combines two strings or expressions. You have the flexibility to add separators and customized formatting with better control over & operator.
You can add a delimiter within the CONCATENATE function to concatenate strings in Power BI.
For example, you can use the below syntax to concatenate 2 column strings with a comma and a space between the columns.
CONCATENATE([Column1], “, “, [Column2])
Power BI doesn’t directly support concatenating arrays or lists of values within DAX expressions.
However, you can follow the steps outlined below:
• Convert the arrays or lists into strings using the text functions such as CONCATENATEX or using the DAX expression to generate a delimited string representation of the array or list
• After converting it to a string, you can then concatenate as needed.
• Alternatively, you might need to reshape your data to better suit Power BI’s model, for instance, by unpivoting or restructuring tables to work with individual values instead of arrays or lists.
Recommended Articles
This has been a guide to Power BI Concatenate. Here we explain how to use concatenate function in Power BI, with examples, and points to remember. You may learn more from the following articles –
Leave a Reply