How to Add Space in CONCATENATE in Power BI

Combining text in Power BI, like merging a "First Name" and "Last Name" column into a "Full Name" column, seems like it should be simple. Yet, a common frustration for newcomers is seeing names smashed together like "JohnSmith" instead of "John Smith." This guide will walk you through exactly how to add spaces - and other delimiters - when combining text in Power BI, covering different methods for different situations.

We'll look at the classic DAX functions, explore the much easier ampersand operator, and even show you the best way to do this in the Power Query Editor to keep your reports running smoothly.

Understanding DAX vs. Power Query for Combining Text

Before jumping into formulas, it's important to know where you're making this change. You have two primary environments in Power BI for this task:

• DAX (Data Analysis Expressions): This is the formula language used for creating calculated columns and measures in your data model after the data has been loaded. This is done in the "Report" or "Data" view. Calculations here are dynamic.
• Power Query (and its M Language): This is the data transformation engine that runs before your data is loaded into the model. You use it to clean, shape, and combine data. This is done in the Power Query Editor. Transformations here happen once during the data refresh.

As a best practice, if you're creating a static column like "Full Name" or "Full Address" that won't change based on user interactions, it's often more efficient to do it in Power Query.

Method 1: Using the Ampersand (&) Operator in DAX

Let’s start with the easiest and most common method. The ampersand (&) operator is the simplest way to join, or concatenate, text strings in DAX. It's clean, intuitive, and flexible.

To use it, you create a new calculated column and link your text fields together with the ampersand. To add a space, you simply include it within double quotes " " as if it were another piece of text to join.

Step-by-Step Instructions:

1. Navigate to the Data view in Power BI and select the table containing your text columns (e.g., 'Customers').
2. From the "Table tools" ribbon, click on New column.
3. In the formula bar, type the following DAX formula. Replace [FirstName] and [LastName] with the names of your actual columns.

Full Name = Customers[FirstName] & " " & Customers[LastName]

Press Enter, and Power BI will create a new "Full Name" column with results like "Jane Doe," "Michael Smith," etc. The space is neatly inserted between the words.

Example: Combining City, State, and Zip Code

The ampersand operator makes it easy to chain multiple text fields and characters. If you wanted to create a "City, State Zip" column, you would do this:

City State Zip = Customers[City] & ", " & Customers[State] & " " & Customers[ZipCode]

This formula first adds the City, then a comma and a space, then the State, then another space, and finally the Zip Code. It's incredibly readable and easy to modify.

Method 2: Using the CONCATENATE Function in DAX

The CONCATENATE function is DAX's formal function for joining strings. However, it has one significant limitation: it can only join two items at a time.

This makes it much clunkier for anything beyond a simple two-part combination. To join a first name and a last name with a space, you have to "nest" one CONCATENATE function inside another.

Example: Creating a Full Name

To recreate our "Full Name" column with this function, the DAX would look like this:

Full Name = CONCATENATE(Customers[FirstName], CONCATENATE(" ", Customers[LastName]))

As you can see, this reads from the inside out: first, it concatenates a space with the last name, creating " Doe". Then, the outer function concatenates the first name with that result, producing "Jane Doe". It works, but it’s not as straightforward as the ampersand method. For this reason, most Power BI developers prefer the & operator for its simplicity.

Method 3: Using the COMBINEVALUES Function (Best for Handling Blanks)

So what happens if a piece of your data is missing? For example, what if you want to create a full name using First, Middleinitial, and Last Name, but some people don’t have a middle initial?

If you use the ampersand method like this:

Full Name With Middle = Customers[FirstName] & " " & Customers[MiddleInitial] & " " & Customers[LastName]

For anyone without a middle initial, you'd get "Jane Doe" - with an ugly double space in the middle. This is where COMBINEVALUES comes in.

COMBINEVALUES is a powerful DAX function designed to join multiple text strings with a delimiter, and it elegantly skips any blank values.

Example: Creating a Full Name and Skipping Blanks

To use COMBINEVALUES, you specify the delimiter (the character to put between the values) first, followed by all the columns you want to join.

Full Name = COMBINEVALUES(" ", Customers[FirstName], Customers[MiddleInitial], Customers[LastName])

With this one simple formula:

• For "John F. Smith", the output is "John F. Smith".
• For "Jane Doe", where the middle initial is blank, the output is "Jane Doe" (no double space!).

This makes COMBINEVALUES the best DAX choice when you're joining a list of items where one or more might be empty.

Method 4: Combining Columns in Power Query

As mentioned earlier, creating a combined column in the Power Query Editor is often the most efficient approach. You have two great options here: the point-and-click interface or a simple custom column formula.

Option A: The "Merge Columns" Feature (No Code Required)

This is the fastest and easiest way to combine text for beginners.

1. From Power BI Desktop, click on Transform data in the "Home" ribbon to open the Power Query Editor.
2. In the editor, find the table you want to modify.
3. Hold down the Ctrl key and click to select the columns you want to combine (e.g., click FirstName, then LastName). The order you click them in determines the final order.
4. Go to the Add Column tab on the ribbon.
5. Click the Merge Columns button.
6. A dialog box will appear. For the separator, choose Space from the dropdown menu.
7. Give your new column a name, such as "Full Name," and click OK.

That's it! Power Query creates the new, merged column for you. Click "Close & Apply" in the top-left corner to load your changes into the data model.

Option B: Using a Custom Column in Power Query

If you prefer writing formulas or need more customization, you can use a custom column. The formula syntax in Power Query's M language is very similar to DAX.

1. In the Power Query Editor, go to the Add Column tab and click Custom Column.
2. A dialog box will open. In the "New column name" field, type "Full Name".
3. In the "Custom column formula" box, type the following (the ampersand & is also used for concatenation in M Language):

= [FirstName] & " " & [LastName]

Click OK. Just like the "Merge Columns" feature, this adds a new column to your table that will be refreshed automatically with your source data.

Choosing the Right Method for You

Here’s a quick summary to help you decide which approach to use:

• For simple, readable combinations in DAX, use the ampersand (&) operator. It's the community standard for a reason.
• If you're joining text in DAX and need to gracefully handle potential blank values to avoid issues like double spaces, use the COMBINEVALUES function.
• For creating new, permanent columns during data transformation (the most efficient way), use the "Merge Columns" or "Custom Column" features in Power Query.

Final Thoughts

Adding a space when concatenating text in Power BI is straightforward once you know your options. Whether you choose the flexibility of DAX's ampersand operator, the robustness of COMBINEVALUES in handling blanks, or the efficiency of a Power Query transformation, you can now easily combine text fields to make your reports cleaner and more readable.

Mastering these kinds of formulas is powerful, but we know firsthand that spending hours tweaking reports and digging through DAX can feel like a departure from your actual job. That's why we built Graphed. Instead of wrestling with syntax, you can just ask questions in plain English like, "Show me a report of sales by Full Name from our Shopify store" and get a live, interactive dashboard instantly. We connect to an ever-growing list of sales and marketing tools to give you the answers you need without requiring you to become a data analyst on the side.