Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My goal is a list with a set of rows for each role_id having the maximum of any scrum team. The final list would have each row appended with the index value.
This is the source table
team_id role_id
Scrum_Team_1 | Developer |
Scrum_Team_1 | Developer |
Scrum_Team_1 | Tester |
Scrum_Team_2 | Developer |
Scrum_Team_2 | Program_Manager |
Scrum_Team_3 | Developer |
Scrum_Team_3 | Developer |
Scrum_Team_3 | Developer |
Scrum_Team_3 | Tester |
Scrum_Team_3 | Program_Manager |
Scrum_Team_3 | Program_Manager |
The final table would be
role_count
Tester1 |
Developer1 |
Developer2 |
Developer3 |
Program_Manager1 |
Program_Manager2 |
Any Ideas? I've being working on this a while. Thanks!
Solved! Go to Solution.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4uKs2ND0lNzI03VNJRckktS83JL0gtUorVIU0yJLW4BFPGCJ82kGRAUX56EZDtm5iXmI6pxBiffsoksTvYmDg3YSiJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [team_id = _t, role_id = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "team_id", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"team_id - Copy.1", "team_id - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"team_id - Copy.1", type text}, {"team_id - Copy.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"team_id - Copy.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"team_id - Copy.2", "team_id"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"role_id", "team_id"}, {{"Count", each Table.RowCount(_), type number}}), #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"team_id"}), #"Grouped Rows1" = Table.Group(#"Removed Columns2", {"role_id"}, {{"MaxCount", each List.Max([Count]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "ListOfOneToMaxCount", each {1..[MaxCount]}), #"Removed Columns3" = Table.RemoveColumns(#"Added Custom",{"MaxCount"}), #"Expanded ListOfOneToMaxCount" = Table.ExpandListColumn(#"Removed Columns3", "ListOfOneToMaxCount"), #"Added Custom1" = Table.AddColumn(#"Expanded ListOfOneToMaxCount", "RoleWithNumber", each [role_id] & Number.ToText([ListOfOneToMaxCount])), #"Removed Columns4" = Table.RemoveColumns(#"Added Custom1",{"role_id", "ListOfOneToMaxCount"}) in #"Removed Columns4"
There are 2 teams with 1 Tester
--> Gives 1 row
Tester1
There are 3 teams with developers, there is one team with 3 developers which is the most developers for all of the teams --> Gives 3 rows
Developer1
Developer2
Developer3
There are 2 teams with program managers, there is on team with 2 program managers which is the most program managers for all of the teams --> Gives 2 rows
Program_Manager1
Program_Manager2
Thanks for your time.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4uKs2ND0lNzI03VNJRckktS83JL0gtUorVIU0yJLW4BFPGCJ82kGRAUX56EZDtm5iXmI6pxBiffsoksTvYmDg3YSiJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [team_id = _t, role_id = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "team_id", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"team_id - Copy.1", "team_id - Copy.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"team_id - Copy.1", type text}, {"team_id - Copy.2", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"team_id - Copy.1"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"team_id - Copy.2", "team_id"}}), #"Grouped Rows" = Table.Group(#"Renamed Columns", {"role_id", "team_id"}, {{"Count", each Table.RowCount(_), type number}}), #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"team_id"}), #"Grouped Rows1" = Table.Group(#"Removed Columns2", {"role_id"}, {{"MaxCount", each List.Max([Count]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "ListOfOneToMaxCount", each {1..[MaxCount]}), #"Removed Columns3" = Table.RemoveColumns(#"Added Custom",{"MaxCount"}), #"Expanded ListOfOneToMaxCount" = Table.ExpandListColumn(#"Removed Columns3", "ListOfOneToMaxCount"), #"Added Custom1" = Table.AddColumn(#"Expanded ListOfOneToMaxCount", "RoleWithNumber", each [role_id] & Number.ToText([ListOfOneToMaxCount])), #"Removed Columns4" = Table.RemoveColumns(#"Added Custom1",{"role_id", "ListOfOneToMaxCount"}) in #"Removed Columns4"
Wow. You are good. Thanks!!
A follow-up question. When the custom column was added the formula was {1..[MaxCount]}. Please point me to the source of this function. Is it a list function or a basic formula? I'm looking for some documentation on the syntax so I can learn. Thanks!
I was able to get a count by scrum team & role. I added a concatenanted column with team_id & role_id --> team_role_id, then added an additional column with
Scrum_Team_1 | Developer | Scrum_Team_1Developer | 2 |
Scrum_Team_1 | Developer | Scrum_Team_1Developer | 2 |
Scrum_Team_1 | Tester | Scrum_Team_1Tester | 1 (KEEP) |
Scrum_Team_2 | Developer | Scrum_Team_2Developer | 1 |
Scrum_Team_2 | Program_Manager | Scrum_Team_2Program_Manager | 1 |
Scrum_Team_3 | Developer | Scrum_Team_3Developer | 3 (KEEP) |
Scrum_Team_3 | Developer | Scrum_Team_3Developer | 3 (KEEP) |
Scrum_Team_3 | Developer | Scrum_Team_3Developer | 3 (KEEP) |
Scrum_Team_3 | Tester | Scrum_Team_3Tester | 1 |
Scrum_Team_3 | Program_Manager | Scrum_Team_3Program_Manager | 2 (KEEP) |
Scrum_Team_3 | Program_Manager | Scrum_Team_3Program_Manager | 2 (KEEP) |
So far, now how do I just select the keepers?
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |