How to split row into column base on the cell value in power query-CodePudding

I have the data as below:

I would like to have an output as below:

I can use VBA to solve this, but Appreciate to any suggestion solve by Power Query. Could you please assist ?

CodePudding user response：

JvdV has the right answer. But that will hard-code the number of columns that the code works against

A dynamic version is below, assuming the two columns are named Column 1 and Column 2

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
x=Table.Group(Source, {"Column1"}, {{"combined", each Text.Combine([Column2], ","), type text}}),
y=List.Transform({1 .. List.Max(List.Transform(x[combined], each List.Count(Text.Split(_,","))))}, each "combined_"& Text.From(_)),
split = Table.SplitColumn(x, "combined", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), y)
in split