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

Question

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 ?

Answer 1

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

A dynamic version for any number of columns (with the first base column named Column1) is:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group( Source, {"Column1"}, { {"data", each Table.AddIndexColumn( Table.SelectColumns( Table.Sort( Table.AddIndexColumn( Table.UnpivotOtherColumns(Table.Skip(Table.DemoteHeaders(_),1), {"Column1"}, "Attribute", "Value"), "Index", 0, 1, Int64.Type),{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}) ,{"Column1", "Value"}), "Index", 0, 1, Int64.Type) , type table}}),
#"Expanded data" = Table.ExpandTableColumn(Group, "data", {"Value", "Index"}, {"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US")[Index]), "Index", "Value")
in #"Pivoted Column"

Alternate sort version:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group( Source, {"Column1"}, {{"data", each Table.AddIndexColumn(Table.SelectColumns(Table.AddIndexColumn(Table.UnpivotOtherColumns(Table.Skip(Table.DemoteHeaders(_),1), {"Column1"}, "Attribute", "Value"), "Index", 0, 1, Int64.Type),{"Column1", "Value"}), "Index", 0, 1, Int64.Type) , type table}}),
#"Expanded data" = Table.ExpandTableColumn(Group, "data", {"Value", "Index"}, {"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US")[Index]), "Index", "Value")
in #"Pivoted Column"