Transpose and group? How do i transpose one column into multiple rows base on value in another column

[Joyner]

Hello, this seems like it should be easy but I can't find ananswer. I have tried the native pivot function,transpose, etc. I have two columns likethis: (sorry for the bad formatting)

Column 1 / Column 2
A / Apple
B / Banana
C / Carrot
A / Airplane
B / Boat
C / Car
A / Anything

So I need Column 1 grouped (unique values only), and Column2 to be spread across as many columns as necessary like:
A / Apple / Airplane / Anything
B / Banana / Boat / Null
C / Carrot / Car / Null

I hope that is clear

Thank you for any help.

 

[alansidman]

Using Power Query /Get and Transform here is the Mcode and the end result

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[td="bgcolor:#ECF0F0, align:center"]G[/td]
[td="bgcolor:#ECF0F0, align:center"]H[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Column1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Apple[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Airplane[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Anything[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Banana[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Boat[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Carrot[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Car[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[/table]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US")[Index]), "Index", "Column2")
in
    #"Pivoted Column"

 

[sandy666]

try

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column2")),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv))
in
    Split[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Custom.1[/td][td=bgcolor:#70AD47]Custom.2[/td][td=bgcolor:#70AD47]Custom.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]Apple[/td][td][/td][td=bgcolor:#E2EFDA]A[/td][td=bgcolor:#E2EFDA]Apple[/td][td=bgcolor:#E2EFDA]Airplane[/td][td=bgcolor:#E2EFDA]Anything[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]Banana[/td][td][/td][td]B[/td][td]Banana[/td][td]Boat[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]C[/td][td=bgcolor:#DDEBF7]Carrot[/td][td][/td][td=bgcolor:#E2EFDA]C[/td][td=bgcolor:#E2EFDA]Carrot[/td][td=bgcolor:#E2EFDA]Car[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]Airplane[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7]Boat[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]Car[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7]Anything[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Joyner]

Thank you for all of the help, Sandy works perfect thanks

 

[sandy666]

You are welcome