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,541
Messages
5,341,070
Members
399,415
Latest member
salterian1974

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top