Power Query - Pivot Column

abaker77

New Member
Joined
Oct 11, 2011
Messages
18
Office Version
  1. 365
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.
MrExcel221213.jpg

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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Please reload your data sheet using XL2BB so that we don't have to retype your data to solve your issues.
 
Upvote 0
So sorry.
Hopefully, the exact range shown in my image is here:
thanks again.

Pivot Column2.xlsx
GHIJKLMNO
2
3original tabledesired result
4
5A/C #Name500009500011500013500017500018500025
6500009MarisMarisGretzkyBirdRuthJeterBrady
7500009MantleMantleFedererNadalStaubach
8500011GretzkyJordanDjokovic
9500011FedererNicklaus
10500011JordanOrr
11500013Bird
12500017Ruth
13500018Jeter
14500018Nadal
15500018Djokovic
16500018Nicklaus
17500018Orr
18500025Brady
19500025Staubach
20
Sheet1
 
Upvote 0
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.
 
Upvote 0
In order to avoid the data being split into blocks, the key is to add an index per grouping on AC.
Power Query:
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
 
Upvote 0
Solution
It did? 😇
Thanks for the feedback. Glad to help.
 
Upvote 0

Forum statistics

Threads
1,223,984
Messages
6,175,786
Members
452,669
Latest member
reeseann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top