Excel Power Query: Split Semicolon-Separated Values into Rows with Conditional Logic
Question details
The user needs to split semicolon-separated values from one column into separate rows and conditionally populate a related column.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A source table contains semicolon-separated values in the REP column, and the related Etap column should be populated only for rows where REP is null.
- Observed behavior
- The desired result is a normalized table with one REP value per row and conditional values transferred to the Etap column when appropriate.
Confirm the source is formatted as an Excel table and note its exact table name, such as Table. Verify that the columns are named REP and Etap, or identify the names that should replace them in the Power Query code.
Use Power Query M code to split REP values and update Etap conditionally
Use this approach when semicolon-separated REP values must become separate rows while Etap is filled only when REP is null.
The transformation first adds a null item before the split values, expands the list into rows, and then uses a custom replacement function to populate Etap only when the corresponding REP value is null. Replace the table and column names if your source uses different names.
In Excel, select any cell in the source table, open the Data tab, choose From Table/Range, and select Advanced Editor in Power Query Editor.
In Advanced Editor, use the following M code and change Table, REP, and Etap to match your data: let Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content], NullRep = Table.ReplaceValue(Source, each [REP], null, (x, y, z) as list => {null} & Text.Split(y, ";"), {"REP"}), ExpandedRep = Table.ExpandListColumn(NullRep, "REP"), UpdatedEtap = Table.ReplaceValue(ExpandedRep, each [REP], null, (x, y, z) as nullable text => if y is null then x else z, {"Etap"}) in UpdatedEtap
Select Done in Advanced Editor, then choose Close & Load on the Home tab. Check that each semicolon-separated REP item appears on its own row and that Etap is populated only where the REP value is null.
Build the transformation through Power Query menus
Use the visual interface when you prefer not to edit the full M query manually.
Move Split-Row Spreadsheet Workflows to WPS Office
WPS Office provides a free, lightweight spreadsheet environment for opening and editing Excel-compatible workbooks. Although Power Query M transformations must be run in Excel, you can migrate the resulting table or recreate the split-row logic with familiar spreadsheet tools while keeping Microsoft Office format compatibility.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, choose Open, and import the Excel .xlsx file containing the source table.
- 2. Review the transformed data: Open the worksheet and check the REP and Etap columns, then use spreadsheet editing tools to adjust rows or values as needed.
- 3. Save in an Excel-compatible format: Select Menu, choose Save As, and save the workbook as .xlsx to preserve compatibility with Microsoft Excel.
Frequently Asked Questions
How do I split semicolon-separated values into rows in Power Query?
Open Power Query Editor, select the REP column, choose Transform > Split Column > By Delimiter, set the delimiter to a semicolon, and select the option to split into rows. In M code, Text.Split([REP], ";") followed by Table.ExpandListColumn performs the same operation.
Why does the M code add a null value before Text.Split?
The expression {null} & Text.Split(y, ";") inserts a null item before the split values. This creates a row where the related Etap value can be retained or conditionally populated after the REP list is expanded.
How can I update Etap only when REP is null?
Use Table.ReplaceValue with a custom function such as if y is null then x else z. Apply the replacement to the Etap column after expanding REP so the function can test the value in each resulting row.
What should I change if my table is not named Table?
Replace Table in Excel.CurrentWorkbook(){[Name="Table"]}[Content] with the exact Excel table name. You should also replace REP and Etap wherever those column names differ in your source.
Can WPS Office run this Power Query M code?
No. Power Query M code is an Excel Power Query feature. WPS Office can open and edit compatible Excel workbooks, but it does not execute Power Query transformations; run the query in Excel before opening the resulting file in WPS Office.
