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

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.