Power Query Problems

Split Values into Rows and Repeat IDs in Excel Power Query

Elise WilliamsElise Williams Nov 18, 2026 868 views

Question details

Split multiple BMO values in one cell into separate rows while retaining the corresponding Incident number.

Split Values into Rows and Repeat IDs in Excel Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
An Excel table has an Incident number in one column and multiple delimited BMO values in another column.
Observed behavior
Each BMO value should appear on its own row, with the related Incident number repeated beside it.
Before you start

Check which delimiter separates the BMO values, such as a comma or semicolon. Make sure the source range has column headers and that each Incident number is on the same row as its BMO values.

Solution 1Recommended

Split the BMO column into rows with Power Query

Use this method to create one row per BMO value and automatically retain the related Incident number.

1
Create a table

Select the source range in Excel, choose Insert > Table, confirm that the table has headers, and select OK.

2
Open Power Query

Click a cell in the table and select Data > From Table/Range.

3
Check the type step

In the Power Query Editor, review Applied Steps. If the automatic Changed Type step causes problems, select the X beside it to remove it.

4
Split into rows

Select the BMO column, then choose Split Column > By Delimiter. Select the delimiter used in the cells, expand Advanced options, choose Split into Rows, and select OK.

5
Load the result

Check that each BMO value has its corresponding Incident number, then select Home > Close & Load to return the transformed data to Excel.

Split the BMO column into rows with Power Query
Remove extra spaces: If spaces remain around the split values, select the BMO column and choose Transform > Format > Trim before loading.
Free Microsoft Office alternative

Work with Excel tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Excel workbooks. Its format compatibility helps you move existing files into WPS, although it does not fix an Excel Power Query issue.

Open and edit Microsoft Excel XLSX and XLS filesOrganize delimited data with spreadsheet text-to-columns toolsUse a familiar spreadsheet interface for everyday table editingMove existing Office documents into a lightweight, free suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the Incident number repeat after the split?

When you select Split into Rows in Power Query, the other column values from the source row are retained alongside each resulting BMO value.

How do I choose the right delimiter?

In Split Column > By Delimiter, select the character used between BMO values, such as a comma or semicolon. Use a custom delimiter if the separator is not listed.

What if Power Query changes my Incident numbers?

In the Power Query Editor, check the Changed Type step under Applied Steps. Remove it if it converts the IDs incorrectly, then set the Incident column to Text if the IDs need to retain leading zeros.

Can I update the result when the source table changes?

After editing the source table, select Data > Refresh All in Excel to rerun the query and update the loaded results.