Splitting and merging columns in Power BI - Step-by-Step Guide

Introduction

Splitting and merging columns helps you clean and organize your data. You can break one column into parts or combine several columns into one. This makes your reports clearer and easier to understand.

When you have a full name column and want to separate first and last names.

When you want to combine city and state columns into one address column.

When a date and time are in one column but you need them separate.

When you want to join product category and product name into a single column for simpler analysis.

When you need to split a column with values separated by commas into multiple columns.

Steps

Step 1: Open your Power BI Desktop file

- Power BI Desktop main window

Your report and data model load and are ready to edit

Step 2: Click

- Transform Data button on the Home tab

Power Query Editor opens showing your data tables

Step 3: Select the column you want to split or merge

- Data preview pane in Power Query Editor

The column is highlighted for editing

Step 4: To split, click Split Column

- Home tab or Transform tab in Power Query Editor

A menu appears with split options like By Delimiter or By Number of Characters

💡 Use 'By Delimiter' if your data has commas, spaces, or other separators

Step 5: Choose the split option and configure it

- Split Column dialog box

Preview shows how the column will split into new columns

Step 6: Click OK

- Split Column dialog box

The original column splits into multiple new columns in the data preview

Step 7: To merge columns, select multiple columns by holding Ctrl and clicking each

- Data preview pane in Power Query Editor

Selected columns are highlighted

Step 8: Click Merge Columns

- Transform tab in Power Query Editor

Merge Columns dialog box opens

Step 9: Choose a separator like space or comma, and enter a new column name

- Merge Columns dialog box

Preview shows the merged column format

Step 10: Click OK

- Merge Columns dialog box

Selected columns merge into one new column in the data preview

Step 11: Click Close & Apply

- Home tab in Power Query Editor

Changes save and data loads back into Power BI Desktop

Before vs After

Before

One column named FullName with values like 'John Smith' and 'Anna Lee'

After

Two columns named FirstName and LastName with values split as 'John' and 'Smith', 'Anna' and 'Lee'

Settings Reference

Split Column by Delimiter

📍 Transform tab > Split Column dropdown

Defines how the column is split based on a character separating values

Default: Comma

Split Column by Number of Characters

📍 Transform tab > Split Column dropdown

Splits the column after a set number of characters

Default: Once

Merge Columns Separator

📍 Transform tab > Merge Columns dialog

Sets the character placed between merged column values

Default: None

New Column Name

📍 Merge Columns dialog

Names the new column created after merging

Default: Merged

Common Mistakes

Splitting a column without choosing the correct delimiter

The data splits incorrectly or not at all if the delimiter does not match the data

Check your data for the exact separator and select that delimiter in the split options

Merging columns without a separator

Values join together without space or punctuation, making them hard to read

Always choose a separator like space or comma when merging columns

Summary

Splitting columns breaks one column into parts based on delimiters or character count.

Merging columns combines multiple columns into one with a chosen separator.

Use Power Query Editor's Transform tab to perform these actions easily.

Practice

1. What does splitting a column in Power BI do?
Splitting means:

easy

A. Breaking one column into multiple columns based on a rule

B. Joining multiple columns into one column

C. Deleting unwanted columns from the table

D. Changing the data type of a column

Solution

1. Step 1: Understand the meaning of splitting

   Splitting means dividing one column into several columns based on a delimiter or rule.

2. Step 2: Compare with other options

   Joining columns is merging, deleting is removing, and changing data type is different from splitting.

3. Final Answer:

   Breaking one column into multiple columns based on a rule -> Option A

4. Quick Check:

   Splitting = Breaking column into parts [OK]

Hint: Splitting breaks one column into parts [OK]

Common Mistakes:

• Confusing splitting with merging
• Thinking splitting deletes columns
• Mixing splitting with data type changes

2. Which of the following is the correct way to merge two columns FirstName and LastName with a space separator in Power Query?

easy

A. Table.RemoveColumns(Source, {"FirstName", "LastName"})

B. Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" "))

C. Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName])

D. Table.TransformColumnTypes(Source, {"FullName", type text})

Solution

1. Step 1: Identify merging syntax in Power Query

   Merging columns uses Table.AddColumn with an expression combining columns using & and a separator.

2. Step 2: Check each option

   Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName]) correctly concatenates FirstName and LastName with a space. Table.RemoveColumns removes columns, Table.SplitColumn splits columns, Table.TransformColumnTypes changes data types.

3. Final Answer:

   Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName]) -> Option C

4. Quick Check:

   Merge = AddColumn with & [OK]

Hint: Use & with space inside AddColumn to merge columns [OK]

Common Mistakes:

• Using SplitColumn instead of AddColumn for merging
• Forgetting the space separator
• Removing columns instead of merging

3. Given a column FullName with values like "John Doe", what will be the result of this Power Query step?

Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" "), {"FirstName", "LastName"})

medium

A. Two new columns named FirstName and LastName with split parts

B. A single column with names joined by space

C. An error because SplitColumn needs a number of columns

D. The FullName column deleted from the table

Solution

1. Step 1: Understand SplitColumn with delimiter

   This function splits the FullName column by space into two columns named FirstName and LastName.

2. Step 2: Check other options

   A single column with names joined by space is merging, not splitting. An error because SplitColumn needs a number of columns is incorrect because specifying new column names is valid. The FullName column deleted from the table is false; the original column is replaced by new columns.

3. Final Answer:

   Two new columns named FirstName and LastName with split parts -> Option A

4. Quick Check:

   SplitColumn with delimiter creates multiple columns [OK]

Hint: SplitColumn with delimiter creates named new columns [OK]

Common Mistakes:

• Thinking SplitColumn merges columns
• Assuming original column remains unchanged
• Believing it causes an error without column count

4. You tried to merge columns City and Country using this code:

Table.AddColumn(Source, "Location", each [City] + ", " + [Country])

But you get an error. What is the likely cause?

medium

A. Missing a space after the comma separator

B. Using + operator instead of & for text concatenation

C. Table.AddColumn cannot merge columns

D. Column names are case sensitive and incorrect

Solution

1. Step 1: Check concatenation operator in Power Query

   Power Query uses & to join text, not + which causes an error.

2. Step 2: Review other options

   Missing space after comma is not an error, AddColumn can merge columns, and case sensitivity depends on source but usually not error here.

3. Final Answer:

   Using + operator instead of & for text concatenation -> Option B

4. Quick Check:

   Use & for text join, not + [OK]

Hint: Use & to join text columns, not + [OK]

Common Mistakes:

• Using + instead of & for text
• Assuming AddColumn can't merge
• Ignoring case sensitivity issues

5. You have a column Address with values like "123 Main St, Springfield, IL". You want to split it into Street, City, and State columns. Which Power Query step correctly splits this?

hard

A. Table.TransformColumns(Source, {"Address", each Text.Split(_, ", ")})

B. Table.AddColumn(Source, "Street", each Text.BeforeDelimiter([Address], ", "))

C. Table.SplitColumn(Source, "Address", Splitter.SplitTextByEachDelimiter({","}), {"Street", "City", "State"})

D. Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(", "), {"Street", "City", "State"})

Solution

1. Step 1: Identify correct delimiter and split method

   The address uses ", " as delimiter, so SplitTextByDelimiter with ", " is correct to split into three columns.

2. Step 2: Evaluate other options

   Table.AddColumn(Source, "Street", each Text.BeforeDelimiter([Address], ", ")) only extracts Street, not full split. Table.SplitColumn(Source, "Address", Splitter.SplitTextByEachDelimiter({","}), {"Street", "City", "State"}) uses SplitTextByEachDelimiter with wrong delimiter format. Table.TransformColumns(Source, {"Address", each Text.Split(_, ", ")}) splits but does not create separate columns.

3. Final Answer:

   Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(", "), {"Street", "City", "State"}) -> Option D

4. Quick Check:

   SplitColumn with correct delimiter and column names [OK]

Hint: Use SplitTextByDelimiter with exact separator and column names [OK]

Common Mistakes:

• Using wrong delimiter like comma without space
• Extracting only one part instead of splitting all
• Not naming new columns after split