Transpose and Pivot data

Hi Richard_Halsall 

This is my solution. Hope it helps. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdZdb4IwFAbgv0K4dhltAWF3+JVpgm4IemEMIdhkJPIxxIv9+5XqtmzSUyDOGDGnxUfeHqq7nTo/qAN1ElWUHZZRWh9WRZGX1TlLqo8wjFnBy4809Oj7OSlpSrMqHOdn9srH1vRI44oeQp9G6aXk0/gtS+IkykJ+5m2x4MX9YKdGxkp/1vjDmE2NwHHZXA09sifWMKm/m7ucKA8KJjqy6zHNvJyAXqb+xn11DFYkHIhSBbE34zwt8lNSUeXHfEJ81q1ogiLW+ouYDaAGcQiLqL9IBKJ1FXGziO+fqg2L5P6pOoGDAVG/e6oLsFdN63eq7gm75auz4J91FesZo2N0oIpHiygp/warNaBgu5oWaYUKl7JJBNvVtHSRSKQiFogWLBrSayQ9grVh1GyFdgr2q2MF4lAarFAUBTuC7hHTsv6lY8cwardCOwU7AUVbkwbbuWOnsIik19inY2cwKt9/unVsMI+9wNkKOhYhjQiD/RYR/7+QJXmpdILXMCzPF/wBaxJ9WJSHi7quKEODK9qwojUq39z75ruBYeEe3z/fLSzK9/gW+e4/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", type text}, {"Date", type date}, {"Name", type text}, {"Opportunity__c", type text}, {"Role_Requirement_Count__c", Int64.Type}, {"Selected_Team__c", type text}, {"Technician_Role__c", type text}, {"Technician_Rope__c", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Opportunity__c", "Selected_Team__c", "Technician_Role__c"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Opportunity__c", "Technician_Role__c"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Opportunity__c", Order.Ascending}, {"Count", Order.Descending}}),
    #"Grouped Rows2" = Table.Group(#"Sorted Rows", {"Opportunity__c"}, {{"Data", each _, type table [Opportunity__c=nullable text, Technician_Role__c=nullable text, Count=number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows2",{{"Data",each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Technician_Role__c", "Count", "Index"}, {"Technician_Role__c", "Count", "Index"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Data", "Index", "Index - Copy"),
    #"Added Prefix" = Table.TransformColumns(#"Duplicated Column", {{"Index", each "Role " & Text.From(_, "en-US"), type text}}),
    #"Added Prefix1" = Table.TransformColumns(#"Added Prefix", {{"Index - Copy", each "Role " & Text.From(_, "en-US") & " Technicians", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Prefix1",{{"Index", "Roles"}, {"Index - Copy", "Role Count"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Roles]), "Roles", "Technician_Role__c"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[#"Role Count"]), "Role Count", "Count"),
    Custom2 = Table.Group(#"Pivoted Column1", {"Opportunity__c"}, List.Transform(List.Skip(Table.ColumnNames(#"Pivoted Column1"),1), (x)=> {x, each List.Max(Table.Column(_, x))})),
    #"Reordered Columns" = Table.ReorderColumns(Custom2, {"Opportunity__c"} & List.Sort(List.Skip(Table.ColumnNames(Custom2), 1), Order.Ascending))
in
    #"Reordered Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdZdb4IwFAbgv0K4dhltAWF3+JVpgm4IemEMIdhkJPIxxIv9+5XqtmzSUyDOGDGnxUfeHqq7nTo/qAN1ElWUHZZRWh9WRZGX1TlLqo8wjFnBy4809Oj7OSlpSrMqHOdn9srH1vRI44oeQp9G6aXk0/gtS+IkykJ+5m2x4MX9YKdGxkp/1vjDmE2NwHHZXA09sifWMKm/m7ucKA8KJjqy6zHNvJyAXqb+xn11DFYkHIhSBbE34zwt8lNSUeXHfEJ81q1ogiLW+ouYDaAGcQiLqL9IBKJ1FXGziO+fqg2L5P6pOoGDAVG/e6oLsFdN63eq7gm75auz4J91FesZo2N0oIpHiygp/warNaBgu5oWaYUKl7JJBNvVtHSRSKQiFogWLBrSayQ9grVh1GyFdgr2q2MF4lAarFAUBTuC7hHTsv6lY8cwardCOwU7AUVbkwbbuWOnsIik19inY2cwKt9/unVsMI+9wNkKOhYhjQiD/RYR/7+QJXmpdILXMCzPF/wBaxJ9WJSHi7quKEODK9qwojUq39z75ruBYeEe3z/fLSzK9/gW+e4/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Custom1 = Table.Combine(Table.Group(#"Promoted Headers","Opportunity__c",{"n",each let a=Table.Group(_,"Technician_Role__c",{"cnt",each List.Count(List.Distinct([Selected_Team__c]))}) in #table({"Opportunity__c"}&List.TransformMany({1..Table.RowCount(a)},each {""," Technican"},(x,y)=>"Role "&Text.From(x)&y),{{[Opportunity__c]{0}}&List.Combine(Table.ToRows(a))})})[n])
in
    Custom1