I have a table with 2 columns. The first column contains duplicate values of a code. The second column contains a name associated with the code. I would like create a table with unique values in column 1 and with multiple columns each containing a name associated with the code. Can I use Power Query? Thanks
Code Name
01404360 BOOTH, Adrian
01404360 LEWIS, David Alexander
01404360 NICHOLSON, Leslie
11772984 BELLIS, Tom Arthur
07277291 OSBORNE, Leigh Ross
07277291 OSBORNE, Maxine
07277291 TYLER, Gregory Stuart
05052644 PERKINS, Antony James
05052644 PERKINS, Nicola Jane
01461813 CANNON, Hugo James
01461813 CANNON, Robert James
01461813 DAWSON, Peter Graham
01461813 SHROSBREE, Richard James
04299559 DEFFLEY, Anthony Francis
04299559 DEFFLEY, Steven Francis
00549113 BROWNE, Niall
00549113 HANCOCK, Gary
00549113 HANCOCK, Jayne
00549113 WALSH, Patrick Martin
03161873 HAYTON, Antony
03161873 HAYTON, Jane
03161873 TRAVES, Alec
03161873 TRAVES, Joseph
03161873 TRAVES, June
03161873 TRAVES, Karen
Code Name 1 Name 2 Name 3 Name 4 Name 5 Name 6
01404360 BOOTH, Adrian LEWIS, David Alexander NICHOLSON, Leslie
11772984 BELLIS, Tom Arthur
07277291 OSBORNE, Leigh Ross OSBORNE, Maxine TYLER, Gregory Stuart
05052644 PERKINS, Antony James PERKINS, Nicola Jane
01461813 CANNON, Hugo James CANNON, Robert James DAWSON, Peter Graham SHROSBREE, Richard James
04299559 DEFFLEY, Anthony Francis DEFFLEY, Steven Francis
00549113 BROWNE, Niall HANCOCK, Gary HANCOCK, Jayne WALSH, Patrick Martin
03161873 HAYTON, Antony HAYTON, Jane TRAVES, Alec TRAVES, Joseph TRAVES, June TRAVES, Karen
Yes, you can use something like this:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Code"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table [Code=number, Name=text]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Code"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Code", "Name","Index"}, {"Code", "Name", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-GB")[Index]), "Index", "Name")
in
#"Pivoted Column"
It basically groups on the code, adding an index column to the table created for each code, then expands that table and pivots using the added index column.
Rory,
When I tried your Mcode, it errored looking for the Index Column that you created in the Group By.
I separated those steps and this worked for me.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Code"}, {{"Data", each _, type table [Code=number, Name=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Index"}, {"Name", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Name")
in
#"Pivoted Column"
HI Alan,
That's odd - I just tried it again and it worked as posted (64bit 365). Would you mind testing this slight alteration?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Code"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table [Code=number, Name=text, Index=number]}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Code"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Code", "Name","Index"}, {"Code", "Name", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-GB")[Index]), "Index", "Name")
in
#"Pivoted Column"
Thank you, both.
I did find that Rory's solutions worked but omitted the code in column 1, but Alan's solution kept the code in column 1.
I now need to figure out how it works !!
Thank you both, again.
Rory
Modified Mcode worked perfectly
Alan
