PQ to transpose column into new headers and sort data...

[Sleeplol]

Hello,
I'll ask this question the best that I can without proper PQ terminology.

In short I'd like PQ to turn this...

... Into this:

Both Columns "Type" and "Specific" are dynamic so I'm hoping PQ can accommodate for this.
I'm currently using VBA to capture "Type" remove dups then Transpose, and index formula spread across many many cells under the Transposed headers. It's a lot of calculation that takes excel a while to update.
Hoping PQ could make this WAY faster.

Thanks for any help.

 

[johnny51981]

Give this a shot.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Type Index" = Table.Group(Source, {"Type"}, {{"Type Index", each Table.AddIndexColumn(_, "Type Index",1,1), type table}}),
    #"Expanded Type Index" = Table.ExpandTableColumn(#"Added Type Index", "Type Index", {"Specific", "Type Index"}, {"Specific", "Type Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Type Index", List.Distinct(#"Expanded Type Index"[Type]), "Type", "Specific"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Type Index"})
in
    #"Removed Columns"

 

[Sleeplol]

Hi Johnny51981,

This works so well. Many thanks. You've saved my WB so much calculation time; it's practically instant now