Split, Group and Pivot Columns
Learn how to split text into separate columns, group rows to calculate summaries, pivot rows into columns, and unpivot columns into rows using Power Query Editor.
1. Introduction to Data Reshaping
Data reshaping changes the structure of a dataset without necessarily changing the meaning of its information. Power Query provides transformations that help organize data into a format suitable for reporting, modeling, and calculations.
Four Important Operations
- Split Column: Divides one column into two or more columns.
- Group By: Groups rows based on selected fields and calculates summary values.
- Pivot Column: Converts distinct values from a column into separate columns.
- Unpivot Columns: Converts multiple columns into attribute-value rows.
Visual 1: Four common ways to reshape and summarize data in Power Query.
2. Splitting a Column
Split Column is useful when a single field contains multiple pieces of information. You can separate values by a delimiter, character position, or another supported splitting rule.
Example: Split a Full Name
Before transformation
After splitting by a space delimiter
Steps to Split a Column
- Open Power BI Desktop and select Home → Transform Data.
- Select the required query in Power Query Editor.
- Select the column you want to split.
- Choose Split Column from the Transform or Home ribbon.
- Select By Delimiter or another suitable option.
- Choose the delimiter, such as a space, comma, or hyphen.
- Select the appropriate splitting option and confirm.
- Rename the resulting columns and inspect the data.
Visual 2: Splitting a text column creates separate fields for analysis.
3. Group By in Power Query
Group By combines rows that share the same value in one or more selected columns. You can calculate totals, counts, averages, minimums, maximums, and other supported aggregations.
Example: Regional Sales Summary
Original sales data
| OrderID | Region | Amount |
|---|---|---|
| 101 | West | 2500 |
| 102 | North | 1800 |
| 103 | West | 3200 |
| 104 | North | 2200 |
| 105 | South | 1500 |
After Group By Region and Sum Amount
| Region | TotalSales |
|---|---|
| West | 5700 |
| North | 4000 |
| South | 1500 |
Steps to Use Group By
- Select the table in Power Query Editor.
- Select Home → Group By.
- Choose the column used to group rows, such as Region.
- Enter a name for the new aggregation column, such as TotalSales.
- Choose Sum as the operation.
- Select Amount as the column to aggregate.
- Confirm and inspect the summarized result.
Visual 3: Group By summarizes transactions by a selected field.
Common Group By Operations
| Operation | Purpose | Example |
|---|---|---|
| Sum | Adds numeric values. | Total sales by region. |
| Count Rows | Counts rows in each group. | Orders per customer. |
| Average | Calculates the mean of numeric values. | Average order amount by region. |
| Minimum | Finds the smallest value. | Lowest sale per category. |
| Maximum | Finds the largest value. | Highest sale per product. |
| All Rows | Retains a nested table for each group. | Keep each region's detailed transactions together. |
4. Pivot Columns
Pivot Column transforms distinct values from one column into separate column headings. It is often used to turn category or period values into a wider table.
Example: Monthly Sales by Region
Before pivoting
| Region | Month | Sales |
|---|---|---|
| West | January | 5000 |
| West | February | 6200 |
| North | January | 4300 |
| North | February | 5100 |
After pivoting Month
| Region | January | February |
|---|---|---|
| West | 5000 | 6200 |
| North | 4300 | 5100 |
Steps to Pivot a Column
- Select the column containing values that should become new headings, such as Month.
- Select Transform → Pivot Column.
- Choose the values column, such as Sales, when prompted.
- Select the appropriate aggregation operation if required.
- Confirm and inspect the resulting table.
5. Unpivot Columns
Unpivot transforms selected columns into attribute-value rows. It is particularly useful when data is stored in a wide format, with each month, department, or category represented by a separate column.
Example: Quarterly Sales
Before unpivoting
| Region | Q1 | Q2 | Q3 |
|---|---|---|---|
| West | 12000 | 15000 | 17000 |
| North | 9000 | 11000 | 12500 |
After unpivoting Q1, Q2, and Q3
| Region | Quarter | Sales |
|---|---|---|
| West | Q1 | 12000 |
| West | Q2 | 15000 |
| West | Q3 | 17000 |
| North | Q1 | 9000 |
| North | Q2 | 11000 |
| North | Q3 | 12500 |
Visual 4: Unpivot converts multiple period columns into attribute-value rows.
Steps to Unpivot Columns
- Select the columns to unpivot, or select the identifier columns that should remain unchanged.
- Open the Transform tab.
- Choose Unpivot Columns or Unpivot Other Columns, depending on the selected fields.
- Review the generated Attribute and Value columns.
- Rename Attribute and Value to meaningful names, such as Quarter and Sales.
- Set suitable data types and inspect the result.
6. Pivot vs. Unpivot
| Feature | Pivot | Unpivot |
|---|---|---|
| Transformation | Values become column headings. | Columns become attribute-value rows. |
| Typical output | Wider table. | Longer table. |
| Example | Month values become January and February columns. | January and February columns become Month and Sales rows. |
| Common use | Cross-tab summaries and comparison layouts. | Preparing period-based data for analysis. |
7. Common Mistakes and Troubleshooting
Split Column Creates Unexpected Results
Check the delimiter, split position, and number of delimiters. Inspect rows containing missing or repeated separators.
Group By Produces Incorrect Totals
Confirm that the correct numeric column and aggregation operation were selected. Check for duplicate records and incorrect data types.
Pivot Produces Errors
Review duplicate combinations of identifier and pivot values. Select an appropriate aggregation where multiple values exist.
Unpivot Creates Unexpected Null Values
Inspect blank source cells and determine whether they should remain null, be replaced, or be handled according to business rules.
New Values Do Not Appear as Expected
Review the source data and applied steps. Confirm that filters or earlier transformations have not removed the required records.
8. Practical Exercise
Exercise A: Split Customer Names
- Import a customer table containing a FullName column.
- Split FullName using a space delimiter.
- Rename the resulting columns FirstName and LastName.
- Inspect rows containing more than two name parts.
Exercise B: Group Sales by Region
- Import a sales transaction table.
- Open Group By and select Region.
- Calculate Sum of Amount and name it TotalSales.
- Compare the totals with the original transactions.
Exercise C: Unpivot Quarterly Data
- Import a table containing Region, Q1, Q2, and Q3.
- Keep Region as the identifier column.
- Unpivot Q1, Q2, and Q3.
- Rename Attribute to Quarter and Value to Sales.
- Check the resulting row count and data types.
Expected outcome: Three prepared datasets demonstrating text splitting, aggregation, and data reshaping.
9. Knowledge Check
Question 1: Which operation separates one text column into multiple columns?
A. Group By
B. Split Column
C. Append Queries
D. Merge Queries
Question 2: Which operation can calculate total sales for each region?
A. Group By
B. Split Column
C. Rename Column
D. Unpivot Columns
Question 3: What happens during a pivot transformation?
A. Rows are always deleted
B. Values from a column become separate columns
C. All data types become text
D. Queries are automatically published
Question 4: What does unpivoting generally do?
A. Converts selected columns into attribute-value rows
B. Deletes all null values
C. Combines unrelated tables automatically
D. Creates report themes
Question 5: Which operation is useful for calculating the average amount per region?
A. Replace Values
B. Split Column
C. Group By with Average
D. Rename Column
View Answers
- B — Split Column.
- A — Group By.
- B — Values from a column become separate columns.
- A — Converts selected columns into attribute-value rows.
- C — Group By with Average.
Summary
In this lesson, you learned how to split columns using delimiters, group rows to calculate summaries, pivot values into separate columns, and unpivot wide datasets into attribute-value rows.
You also explored practical examples, common transformation errors, and ways to reshape data into a clean and consistent structure for Power BI reporting.