How to combine two columns in Power Query?

Are you struggling with data spread across multiple columns that you need to merge into a single, meaningful field? Perhaps you have customer names split into first and last name columns, or address components in separate fields that need to be combined into a complete address.

Before diving into column combinations, you might want to learn about extracting values from Power BI columns to ensure your source data is clean.

Data Model Overview

We’ll work with a Customer Information table containing separate columns for customer details that need to be combined.

Step-by-Step Guide to Combining Columns

1. Using Power Query Editor

1. Open Power Query Editor
2. Select the columns you want to combine
3. Right-click and choose “Merge Columns”
4. Select your separator (space, comma, or custom)

2. Using Custom Column Formula

Follow these steps for more control:

1. Click “Add Column” in the ribbon
2. Select “Custom Column”
3. Use this formula pattern:

powerqueryCopy= [Column1] & " " & [Column2]

3. Advanced Column Combination Using M Code

For complex combinations:

powerqueryCopylet
    Source = Table.AddColumn(
        YourTable,
        "CombinedColumn",
        each Text.Combine({
            [FirstName],
            [MiddleName],
            [LastName]
        }, " "),
        type text
    )
in
    Source

4. Handling Null Values

Implement error handling:

powerqueryCopylet
    Source = Table.AddColumn(
        YourTable,
        "FullAddress",
        each Text.Combine(
            List.RemoveNulls({
                [StreetAddress],
                [City],
                [State],
                [ZipCode]
            }), 
            ", "
        ),
        type text
    )
in
    Source

Example: Customer Database Management

Consider a retail database where customer information needs standardization:

1. Combine name fields for consistent display
2. Create full address field for shipping labels
3. Merge phone country codes with numbers
4. Concatenate product codes with descriptions

Combine Power Query columns without hiding nulls or type problems

Start with one reversible comparison and preserve the current state. Define separators and expected output first. Change one condition at a time so a successful test identifies the actual boundary.

Check the deciding layer

Convert nontext types explicitly. Decide how null and blank values should behave. Use a non-sensitive sample and the normal user account when possible. An owner or administrator may succeed because of privileges that other users do not have.

Preserve source columns until validation completes. Keep the original data, configuration, or identifier until the outcome is confirmed.

Verify the ordinary workflow

Test locale-sensitive dates and numbers. Compare row counts and error counts after refresh. Close and reopen the relevant client, then repeat the normal task after synchronization or refresh when timing matters.

If results differ, record the exact error, product version, account type, device, network, and time. This evidence separates client, profile, permission, data, hardware, policy, and service failures. Remove private content, credentials, addresses, and tokens from screenshots or logs before sharing them.

Have another authorized user repeat the shortest safe test when shared access is involved. Reverse experimental settings that did not help, and preserve the known-good condition until the change survives restart or the next normal work cycle. Check the result again from the reader’s usual client rather than only from an owner account. Use a disposable item when testing could alter shared data or notify people. Keep a concise rollback note for future updates or device replacement. Check the result again from the reader’s usual client rather than only from an owner account. Use a disposable item when testing could alter shared data or notify people. Keep a concise rollback note for future updates or device replacement. Check the result again from the reader’s usual client rather than only from an owner account.

Troubleshooting Common Issues

• Error: “Cannot read property of null” Solution: Use List.RemoveNulls() function before combining
• Unwanted Spaces in Results Solution: Apply Text.Trim() to individual columns before combining
• Special Characters Causing Issues Solution: Use Text.Clean() function on source columns

Want to learn more about data transformation? Check out our guide on converting text columns in Power BI for additional techniques.