Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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
This worked great Thanks Daniel
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.
This worked great Thanks