Power Query - Pivot Column

[abaker77]

Hello,
I have what I think can be solved with a simple Power Query feature - "Pivot Column", but I can't get it to work.

The image above should explain exactly what I'm hoping for.
Cols G-H contain my original table - with 2 columns: account # and name; these may be in any order and sequence.
What I need is cols J-O which lists the unique account #'s across the top and every name that belongs to the account #, beneath it.
This is an ever-changing list to once the table is updated, I'm hoping I can simply hit Ctrl-Alt-F5 to Refresh and re-generate my desired result.
I think 'Pivot Column' should work but I can't figure out how to do it.
Surely there are geniuses out there (?); I'd be so grateful if someone could let me know.
THANK YOU SO MUCH!

 

[alansidman]

Please reload your data sheet using XL2BB so that we don't have to retype your data to solve your issues.

 

[abaker77]

So sorry.
Hopefully, the exact range shown in my image is here:
thanks again.

Pivot Column2.xlsx
	G	H	I	J	K	L	M	N	O
2
3	original table			desired result
4
5	A/C #	Name		500009	500011	500013	500017	500018	500025
6	500009	Maris		Maris	Gretzky	Bird	Ruth	Jeter	Brady
7	500009	Mantle		Mantle	Federer			Nadal	Staubach
8	500011	Gretzky			Jordan			Djokovic
9	500011	Federer						Nicklaus
10	500011	Jordan						Orr
11	500013	Bird
12	500017	Ruth
13	500018	Jeter
14	500018	Nadal
15	500018	Djokovic
16	500018	Nicklaus
17	500018	Orr
18	500025	Brady
19	500025	Staubach
20
Sheet1

 

[GraH]

Looks more like a transpose is required, then promote first row as header.

EDIT: got it, there are duplicated A/C's.
Try this:
Add an index column, select A/C, pivot, use name as aggregated field and select do not aggregate in the advanced setting.
Then remove the index column.

 

[GraH]

In order to avoid the data being split into blocks, the key is to add an index per grouping on AC.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Set_all_txt = Table.TransformColumnTypes(Source,List.Transform(Table.ColumnNames(Source), each {_, type text})),
    Group_all = Table.Group(Set_all_txt, {"A/C #"}, {{"All", each _, type table [#"A/C #"=nullable text, Name=nullable text]}}),
    Add_index = Table.TransformColumns(Group_all,{{"All", each Table.AddIndexColumn(_, "Index", 1,1), type table}}),
    Expand_all = Table.ExpandTableColumn(Add_index, "All", {"Name", "Index"}, {"Name", "Index"}),
    Pivot_AC = Table.Pivot(Expand_all, List.Distinct(Expand_all[#"A/C #"]), "A/C #", "Name"),
    Remove_index = Table.RemoveColumns(Pivot_AC,{"Index"}),
    Reset_types = Table.TransformColumnTypes(Remove_index,List.Transform(Table.ColumnNames(Remove_index), each {_, type text}))
in
    Reset_types

 

[abaker77]

That actually worked !
Thank you so much.

 

[GraH]

It did?
Thanks for the feedback. Glad to help.

 

[alansidman]

deleted. GraH had a cleaner solution