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]}, ", ")