Power Query - Transpose unique values and get matching values in rows

1
votes

I am trying to transform a very simple input_table

enter image description here

into this output_table:

enter image description here

I am basically trying to:

  • Get unique items in first column
  • Transpose and promote these items as column headers
  • Get matching values (from the second column in input_table) under each unique header

I am using Power Query because I need the output_table to dynamically update each time I add records to input_table, but any other dynamical solution is accepted (Array Formulas, intermediate Tables, etc...). No VBA.

1
excelpivotuniquetransposepowerquery

1 Answers

1
votes

Assuming your table is called Table1 and is structured in the way shown in the screenshot, I think this Power Query query should do what you want:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[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

Chris