Split Comma-Separated Values into Multiple Rows in Power Query
Question details
The user needs to convert a single column containing comma-separated values into multiple separate rows while duplicating the adjacent row data, such as ID and Date, for each new item.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Data cleaning and transformation where grouped data in a single cell must be expanded into a normalized format for accurate analysis and reporting.
- Observed behavior
- Multiple distinct values are trapped in a single cell separated by commas, preventing independent row-level analysis or database-style structuring.
Ensure your dataset is formatted as an Excel Table (Ctrl+T) before importing it into Power Query to guarantee that future data additions are automatically processed.
Use the 'Split Column by Delimiter' Feature (Recommended)
This is the easiest and most user-friendly method to split comma-separated values into separate rows directly through the Power Query graphical interface.
Power Query has a built-in feature designed to split columns by any delimiter. By changing a single setting in the advanced options, you can force the split values to populate new rows instead of new columns, automatically duplicating the adjacent data for you.
Select any cell inside your Excel table, go to the 'Data' tab on the ribbon, and click 'From Table/Range'. This will open the Power Query Editor.
Right-click the header of the column that contains your comma-separated values (e.g., 'Affected Units'). Select 'Split Column' from the context menu, then choose 'By Delimiter'.
In the dialog box, ensure the delimiter is set to 'Comma'. Click on 'Advanced options' to expand the menu, and under 'Split into', select the 'Rows' radio button.
Click 'OK'. The values will be split into individual rows while the related line, ID, and date columns duplicate automatically. Finally, click 'Close & Load' to return the transformed data to Excel.
Use Advanced Editor and M Code
For advanced users who prefer writing or pasting custom M Code to achieve specific transformations and re-indexing.
Handle Complex Data Easily with WPS Spreadsheet
While Power Query is primarily a Microsoft ecosystem tool, WPS Office provides a powerful, free, and lightweight alternative for data analysis. WPS Spreadsheet offers comprehensive data processing tools, advanced array formulas, and seamless compatibility with Microsoft Excel files, enabling you to clean and transform datasets without expensive subscriptions.
- 1. Download and Install: Visit the official WPS Office website to download the free suite for Windows, Mac, or Linux.
- 2. Open your Excel File: Launch WPS Spreadsheet and securely open your existing .xlsx files with full formatting retained.
- 3. Process your Data: Utilize the 'Text to Columns' feature under the Data tab or modern dynamic formulas to quickly separate and analyze complex cell data.
Frequently Asked Questions
Can I split values into columns instead of rows in Power Query?
Yes. When using the 'Split Column by Delimiter' feature, expand the 'Advanced options' in the dialog box and select 'Columns' instead of 'Rows'. This will create new columns (e.g., Column1, Column2) for each separated value.
How do I retain the original row IDs when splitting data to new rows?
Power Query handles this automatically. When you choose to split a column into rows, the query engine automatically duplicates the data from all adjacent columns (such as ID, Date, or Name) for every new row created.
Is there an Excel formula to split text into rows without relying on Power Query?
Yes. In newer versions of Excel, you can use the TEXTSPLIT function. For example, the formula =TEXTSPLIT(A2, ", ") splits text into columns, but combining it with TOCOL or using =TEXTSPLIT(A2, , ", ") (skipping the column delimiter argument) will spill the separated values directly into rows.
