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

Question

I am trying to transform a very simple input_table

into this output_table:

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.

Answer 1

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