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
 

acatalux

New Member
Joined
Jul 19, 2013
Messages
9
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>
 

lager1001

New Member
Joined
May 17, 2019
Messages
19
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?
 

Forum statistics

Threads
1,078,365
Messages
5,339,768
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top