Converting unique values in first column into column headers and group second column values accordingly (transpose/pivot)

acatalux

New Member
Joined
Jul 19, 2013
Messages
9
Hello,
I have been struggling quite a bit with what I want to achieve.
My input is a very simple table, like this:

TypeValue
Avalue1
Avalue2
Bvalue3
Avalue4
Bvalue5
Cvalue6
Cvalue7
Avalue8
Bvalue9
Cvalue10

<tbody>
</tbody>


I would like to achieve this table starting from my input table:

ABC
value1value3value6
value2value5value7
value4value9value10
value8

<tbody>
</tbody>


I have been trying using Power Query, simple Excel Tables and Array Formulas and other techniques, but I really can't get it out.

Ideally, as the input_table is updated, I would like the output_table to be dinamically updated/expanded as well, through Data --> Refresh All with Power Query or through formulas recalculation with Excel formulas.

Thank you in advance,
Andrea Catalucci
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I eventually solved it through the amazing help of Cristopher Webb.

Given the input_table in the initial post, this code will accomplish what I wanted and return the intended output_table:

Code:
[COLOR=#222426][FONT=Consolas]let
[/FONT][/COLOR]<code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 0px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: inherit;">    Source = Excel.CurrentWorkbook(){[Name="input_table"</code><code style="margin: 0px; padding: 0px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: inherit;">[FONT=Consolas]]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", type text}}),
    GroupedRows = Table.Group(ChangedType, {"Type"}, {{"DistinctValues", each _[Value]}}),
    Output = Table.FromColumns(GroupedRows[DistinctValues], GroupedRows[Type])
in
    Output
[/FONT]</code>
 
Upvote 0
I use this function a lot in my work. Is there a way to do this for the indicated columns/rows but retain all other columns in the table?
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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