PowerQuery pivot data table sideways

Question

I would like to do table/contingency table from a table which have these data:

A 1
B 1
C 1
A 2
C 2
C 3

to have a contingency table/table like this:

A 1 2
B 1
C 1 2 3 

and so on I tried with power query dont agregate contingency table but doesnt work well cuz i need 3rd column and when i have in 3rd column only 3 values it only gives me contingency table with max 3 columns A 1 2 3 and in other data cuz be more ... Tried contingency table it shows me sum of values on 2nd column but i want to see all values in those columns ... Any good idea for this ?

Answer 1

In powerquery, group on Column1, use operation All Rows

Change each _ to be each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type) in the formula bar. Expand all the columns. Click select the index column, Transform ... Pivot Column ... and use Column2 as values column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Column2", "Index"}, {"Column2", "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", "Column2", List.Sum)
in #"Pivoted Column"