I have tried Excel Power Query option to Split Comma Seprated Values into Rows. It working Well. But I want to Keep original row as it is.

Because Power Query Deletes Original Row and Generates New. I want to keep original row & Generate new rows below that. by comma delimiter. How can we do this?

Expectation:

I have Data Rows like
1 - name - A,B
2 - name - A,B

It should Convert Like This.
1 - name - A,B
1 - name - A
1 - name - B
2 - name - A,B
2 - name - A
2 - name - B

CodePudding user response：

Ok got the Simple solution.

1. Just Split All Rows by Comma Delimiter 'using power query'. we get "After Split Rows"
2. Then Paste All Original Rows Above "After Split Rows"
3. Add the filter to numbers/id row & use "Sort smallest to largest" and Done.

We get the Expected Result with the Original Rows & Split Rows Below them, as shown in above question.

CodePudding user response：

Use 2 imports.

What Power Query passes across to Excel is the model, and this can contain more than one table or process.

Import your data to be split. Carry out your Split by Delimiter into Rows. This will give you a table which has your separated values - Table After Split.

Import your data again as Table Original.

Append Table After Split to Table After Split to produce Table Combined.

Drop Table Original and Table After Split as they are no longer needed and are wasting memory.

Now what you have remaining in your model is your combined table.