Split Values into Rows and Repeat IDs in Excel Power Query
Question details
Split multiple BMO values in one cell into separate rows while retaining the corresponding Incident number.

- 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.
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.
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.
Select the source range in Excel, choose Insert > Table, confirm that the table has headers, and select OK.
Click a cell in the table and select Data > From Table/Range.
In the Power Query Editor, review Applied Steps. If the automatic Changed Type step causes problems, select the X beside it to remove it.
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.
Check that each BMO value has its corresponding Incident number, then select Home > Close & Load to return the transformed data to Excel.

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.

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.




