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
- Open Power Query Editor
- Select the columns you want to combine
- Right-click and choose “Merge Columns”
- Select your separator (space, comma, or custom)
2. Using Custom Column Formula
Follow these steps for more control:
- Click “Add Column” in the ribbon
- Select “Custom Column”
- 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:
- Combine name fields for consistent display
- Create full address field for shipping labels
- Merge phone country codes with numbers
- 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.