Transpose and Pivot data

[Richard_Halsall]

Hi

I am struggling to make the m code dynamic when transposing and pivoting this table

Into an output like this

The issue I am facing is that sometimes there may be 3 roles, sometimes 4 roles other times 6 roles so it can't be coded as attached.

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}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"Opportunity__c", "Technician_Role__c"}, {{"Technician Required", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Opportunity__c", Order.Ascending}}),
    T = Table.Buffer(Table.AddIndexColumn(#"Sorted Rows", "Order", 0, 1, Int64.Type)),
    AddIndex = Table.FromRecords(
        List.Generate(
            () => [Order = 0, Index = 1],
            each [Order] < Table.RowCount(T),
            each [
                    Order = [Order] + 1,
                    Index = if T{Order}[Opportunity__c] = T{[Order]}[Opportunity__c] then [Index] + 1 else 1
                ]
        ),
        type table [Order = Int64.Type, Index = Int64.Type]
    ),
    #"Merged Queries1" = Table.NestedJoin(T, {"Order"}, AddIndex, {"Order"}, "Index", JoinKind.LeftOuter),
    #"Expanded NewID" = Table.ExpandTableColumn(#"Merged Queries1", "Index", {"Index"}, {"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewID",{{"Index", "Role"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Order"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Role", "Role No"}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Technician Required", type text}}, "en-GB"),{"Technician_Role__c", "Technician Required"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Merged Columns", {{"Role No", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Merged Columns", {{"Role No", type text}}, "en-GB")[#"Role No"]), "Role No", "Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Role 1", "Role 1 Technicians"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "2", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Role 2", "Role 2 Technician"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "3", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Role 3", "Role 3 Technician"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "4", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Role 4", "Role 4 Technician"})
in
    #"Split Column by Delimiter3"

Any help would be much appreciated Thanks 

[v-jingzhang · Accepted solution]

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.

[wdx223_Daniel · Accepted solution]

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

[Richard_Halsall]

This worked great Thanks Daniel

[v-jingzhang · Accepted solution]

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.

[Richard_Halsall]

This worked great Thanks