Introduction
When working with data in Power Query, you often need to split or merge columns to structure information correctly. Whether you are dividing full names into first and last names or combining multiple fields into a single column, Power Query provides powerful tools for these tasks.
In this guide, we’ll cover step-by-step methods for splitting and merging columns in Power Query and best practices to keep your data clean and structured.
1. How to Split Columns in Power Query
Splitting columns is useful when you need to separate data into multiple fields.
Methods to Split Columns:
By Delimiter – Splitting based on characters like commas, spaces, or hyphens. By Number of Characters – Splitting at a specific character count. By Using Custom Logic – Splitting with advanced functions using M Language.
Steps to Split a Column by Delimiter:
- Select the column you want to split.
- Click Transform → Split Column → By Delimiter.
- Choose the delimiter type (e.g., space, comma, or custom character).
- Select the splitting method: Each occurrence or At the left-most/right-most delimiter.
- Click OK and review the new split columns.
Example:
| Full Name | First Name | Last Name |
|---|---|---|
| John Doe | John | Doe |
| Jane Smith | Jane | Smith |
2. How to Merge Columns in Power Query
Merging columns is useful when you need to combine multiple fields into one, such as creating full names from first and last names.
Steps to Merge Columns:
- Select the columns you want to merge (hold Ctrl to select multiple).
- Click Transform → Merge Columns.
- Choose a separator (e.g., space, comma, or custom value).
- Enter a new column name.
- Click OK to apply the changes.
Example:
| First Name | Last Name | Full Name |
|---|---|---|
| John | Doe | John Doe |
| Jane | Smith | Jane Smith |
3. Best Practices for Splitting and Merging Columns
Use consistent delimiters when splitting text-based data. Check for null or missing values before merging columns. Rename newly created columns for clarity. Use Power Query formulas for advanced custom splitting or merging.
Conclusion
Splitting and merging columns in Power Query helps improve data organization and analysis in Power BI. By following these techniques, you can ensure your data is well-structured and ready for reporting.