Splitting and merging columns in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a clean sales report where the 'Customer Name' column is split into 'First Name' and 'Last Name', and the 'City' and 'State' columns are merged into a single 'Location' column.

📊 Data: You have a sales data table with columns: Order ID, Customer Name (full name), City, State, Product, Quantity, and Sales Amount.

🎯 Deliverable: A cleaned sales report table with separate 'First Name' and 'Last Name' columns and a merged 'Location' column combining City and State.

Sample Data

Order ID	Customer Name	City	State	Product	Quantity	Sales Amount
1001	John Smith	Seattle	WA	Laptop	2	2400
1002	Mary Johnson	Portland	OR	Tablet	1	600
1003	James Williams	San Francisco	CA	Smartphone	3	1800
1004	Patricia Brown	Denver	CO	Monitor	1	300
1005	Michael Davis	Seattle	WA	Keyboard	4	200
1006	Linda Miller	Portland	OR	Mouse	5	125
1007	Robert Wilson	San Francisco	CA	Printer	1	150
1008	Barbara Moore	Denver	CO	Desk Lamp	2	80

1

Step 1: Split the 'Customer Name' column into two new columns: 'First Name' and 'Last Name'. Use space as the delimiter.

In Power Query Editor, select 'Customer Name' column, then choose 'Split Column' > 'By Delimiter' > select 'Space' > split at the left-most delimiter.

Expected Result

Two new columns: 'First Name' (e.g., 'John') and 'Last Name' (e.g., 'Smith') appear in the table.

2

Step 2: Remove the original 'Customer Name' column after splitting.

Right-click the 'Customer Name' column and select 'Remove'.

Expected Result

The table no longer has the 'Customer Name' column, only 'First Name' and 'Last Name'.

3

Step 3: Merge the 'City' and 'State' columns into a new column called 'Location' with a comma and space separating them.

Select 'City' and 'State' columns, then choose 'Merge Columns' > Separator: Custom > ', ' > New column name: 'Location'.

Expected Result

A new 'Location' column appears with values like 'Seattle, WA'.

4

Step 4: Remove the original 'City' and 'State' columns after merging.

Right-click the 'City' and 'State' columns and select 'Remove'.

Expected Result

The table no longer has 'City' and 'State' columns, only the merged 'Location' column.

5

Step 5: Load the cleaned data back to Power BI report view for further analysis or visualization.

Click 'Close & Apply' in Power Query Editor.

Expected Result

The data model now contains the cleaned table with 'First Name', 'Last Name', 'Location', and other original columns.

Final Result

Order ID | First Name | Last Name | Location         | Product    | Quantity | Sales Amount
-------------------------------------------------------------------------------------------
1001     | John       | Smith     | Seattle, WA      | Laptop     | 2        | 2400
1002     | Mary       | Johnson   | Portland, OR     | Tablet     | 1        | 600
1003     | James      | Williams  | San Francisco, CA| Smartphone | 3        | 1800
1004     | Patricia   | Brown     | Denver, CO       | Monitor    | 1        | 300
1005     | Michael    | Davis     | Seattle, WA      | Keyboard   | 4        | 200
1006     | Linda      | Miller    | Portland, OR     | Mouse      | 5        | 125
1007     | Robert     | Wilson    | San Francisco, CA| Printer    | 1        | 150
1008     | Barbara    | Moore     | Denver, CO       | Desk Lamp  | 2        | 80

✓Splitting the full customer name into first and last names makes it easier to analyze customer data separately.

✓Merging city and state into a single location column simplifies geographic grouping and reporting.

✓The cleaned data is ready for creating reports or dashboards with clearer customer and location details.

Bonus Challenge

Create a new calculated column that combines 'First Name' and 'Last Name' back into a single 'Customer Full Name' column but in the format 'Last Name, First Name'.

Show Hint

Use Power Query's 'Add Column' feature with a custom formula: Text.Combine({[Last Name], [First Name]}, ", ")

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