Split Column by Delimiter
& Inserting Steps Mid-Query
Expand multi-value cells into rows using a delimiter, handle the blank-row problem caused by double delimiters, and learn how to insert a fix at any point in an existing query without losing steps that follow it.
It's common to receive data where multiple values are packed into one cell — for example, a Products column where each order lists several items separated by semicolons. Power Query's Split Column by Delimiter can expand these either into new columns (one per value) or into new rows (one per value). Splitting into rows is almost always the right choice for analysis, as it keeps data in a flat, one-row-per-item structure.
This lab also teaches a critical workflow skill: inserting a transformation step between two existing steps. When you discover a data problem (like double semicolons creating blank rows), you don't need to start over. Click back to the right step in the Applied Steps pane, apply your fix, and Power Query re-runs everything below it automatically — leaving all your later steps intact.
| # | Steps | Directions |
|---|---|---|
| 1 | Open File 2D as an Excel file. Notice that the Products Ordered tab contains a table of data called Customer Orders. | |
| 2 | Close File 2D and return to your working Power Query file. | |
| 3 | Create a new query to reference Excel File 2D and select the "CustomerOrders" table of data instead of a sheet. | |
| 4 | First, try splitting the Products column using a semi-colon delimiter. Notice that we have 4 columns of Product data. | |
| 5 | Delete the previous step. | |
| 6 | Split the Products column into rows using a delimiter. | |
| 7 |
Notice that we have a blank row. Click on the previous step called Navigation to find out why — Row 6 has two consecutive semi-colons ;; which produce an empty value when split.
|
|
| 8 |
Insert a Replace Values step at the Navigation step to replace all double semi-colons with a single one in the Products column — so the blank row never appears.
▲ Applied Steps
Source
Navigation
Replaced Value ← insert here
Split Column by Delimiter
Value To Find
;;
Replace With
;
|
|
| 9 | Click on the Split Column by Delimiter step to ensure any subsequent steps are placed after this one. | |
| 10 | Change data types as appropriate. | |