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

v	A	B	C	D	E	F	G	H
1	Column1	1	2	3	4	5	6	7
2	A	Apple			Airplane			Anything
3	B		Banana			Boat
4	C			Carrot			Car

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]

Column1	Column2		Column1	Custom.1	Custom.2	Custom.3
A	Apple		A	Apple	Airplane	Anything
B	Banana		B	Banana	Boat
C	Carrot		C	Carrot	Car
A	Airplane
B	Boat
C	Car
A	Anything

 

[Joyner]

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

 

[sandy666]

You are welcome