How to Transpose unique values and get matching values in rows

Question

So, I'm attempting to transform this input table.

into this output table.

So, I've to do the following;

• Get unique items in the first column
• Transpose and promote these items as column headers
• Get matching values under each unique header

 I need the output table to dynamically update each time. No VBA.

Answer 1

I think this 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