How to Concatenate in Power BI Query Editor

Combining text from different cells or columns is one of those fundamental data prep tasks you'll find yourself doing all the time. Whether you have "First Name" and "Last Name" columns you need to join into a single "Full Name" column, or you want to create a descriptive label from a city and a state, learning how to concatenate is essential. This guide will walk you through several easy ways to combine text directly within Power BI's powerful Query Editor.

What Does "Concatenate" Actually Mean?

In the simplest terms, to "concatenate" means to link or join things together in a chain or series. In the world of data, it refers to the process of combining multiple strings of text into a single string. It's a simple concept but incredibly useful for making your data cleaner, more readable, and easier to analyze.

Here are a few common scenarios where you’ll need to concatenate data:

• Creating Full Names: The most classic example. Your data contains separate columns for FirstName and LastName, but for your report, you need a single FullName column.
• Building Readable Addresses: Your data source has City, State, and ZipCode in separate fields. You can concatenate them into a single, nicely formatted address string like "San Francisco, CA 94107".
• Generating Unique IDs: Sometimes you need to create a unique identifier by combining existing fields. For example, you might merge a OrderID with a ProductSKU to create a unique key for each line item, like "ORD1001-SKU_A5C".
• Creating Dynamic Chart Labels: You may want to combine a category name with its corresponding value to create explicit labels for a bar chart or other visualization, such as "Q2 Sales: $55,000".

Mastering this simple transformation cleans up your raw data, making your final reports and dashboards far more intuitive and professional.

Getting Started: Navigating to the Power Query Editor

To begin concatenating in Power BI, you'll need to first load your data into the Power Query Editor. This environment allows you to manipulate and prepare your data before it's presented in Power BI's visuals.

Step 1: Get Data

Launch Power BI Desktop and start by loading in your data. Then, navigate to the Home tab and click on Get Data. Connect your respective sources. If the results aren't as expected, try clicking "Transform Data" again or contacting support for assistance.

Step 2: Choose Columns for Merging

When combining more than one column's data, use the CTRL key to select all the fields you want to merge with concatenate. List the primary and secondary columns in the order they should appear in your final draft.

Step 3: Step-by-Step with the Text.Combine Function

Although it might look daunting, using Text.Combine can be straightforward. Let’s create a full address field by combining Address, City, State, and ZipCode fields.

1. Navigate to the Add Column tab in the Power Query Editor and click on Custom Column.
2. In the pop-up dialogue, name your column Mailing Address.
3. In the custom column formula field, enter: `Text.Combine({[Address], [City], [State], [ZipCode]}, ", ")`
4. Click OK, and you’ll have a newly designed Mailing Address field that combines everything into an easy-to-read format.

Dealing with Null or Blank Values

Concatenating fields using the "&" operator might sometimes result in unwanted or incomplete values when a column contains blank or null values. To address this, use the following formula:

if [FirstName] is null then "" else [FirstName] & " " & [LastName]

This formula checks if the first column is blank or null and then adds a space in place. This ensures you don't have a "null" output, making your data look more professional.

Final Thoughts

Concatenation is a simple yet powerful tool when working with data in Power BI. While the "&" operator is often used for basic operations, Power BI's tools allow you to handle complex scenarios easily. Our team at Graphed offers a free 2-week trial. Let’s work together to streamline your workflow and achieve your data goals.