Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

MAX count of Indexed rows by group

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_1Developer
Scrum_Team_1Developer
Scrum_Team_1Tester
Scrum_Team_2Developer
Scrum_Team_2Program_Manager
Scrum_Team_3Developer
Scrum_Team_3Developer
Scrum_Team_3Developer
Scrum_Team_3Tester
Scrum_Team_3Program_Manager
Scrum_Team_3Program_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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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.

 

Anonymous
Not applicable

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"
Anonymous
Not applicable

Wow. You are good. Thanks!!

Anonymous
Not applicable

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!

Anonymous
Not applicable

This is my manual intervention. It's not possible through the UI. It just so happens that I have read the whole documentation on M, so I know it throughout.

When I work with something, I always get to know my tools, so that I know exactly what I'm doing and why 🙂

Best
Darek
Anonymous
Not applicable

OK, now it's much clearer. Thanks.

Best
Darek
Anonymous
Not applicable

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 

 

team_role_count = SUMX(Test_Table, IF( EARLIER(Test_Table[team_role_id]) = Test_Table[team_role_id],1,0))
 
I now get 
 
team_id                role_id                               team_role_id                                Count
Scrum_Team_1DeveloperScrum_Team_1Developer2
Scrum_Team_1DeveloperScrum_Team_1Developer2
Scrum_Team_1TesterScrum_Team_1Tester1     (KEEP)
Scrum_Team_2DeveloperScrum_Team_2Developer1
Scrum_Team_2Program_ManagerScrum_Team_2Program_Manager1
Scrum_Team_3DeveloperScrum_Team_3Developer3      (KEEP)
Scrum_Team_3DeveloperScrum_Team_3Developer3      (KEEP)
Scrum_Team_3DeveloperScrum_Team_3Developer3      (KEEP)
Scrum_Team_3TesterScrum_Team_3Tester1
Scrum_Team_3Program_ManagerScrum_Team_3Program_Manager2      (KEEP)
Scrum_Team_3Program_ManagerScrum_Team_3Program_Manager2      (KEEP)

 

So far, now how do I just select the keepers?

Anonymous
Not applicable

You should avoid calculated columns and use them only if you don't have any other option. But you do have an option - I just showed you how to calculated this in Power Query. Calculated columns are not compressed efficiently but when you created them in Power Query, THEY ARE.

Best
Darek
Anonymous
Not applicable

Unclear. The final table does not look correct. Please give us a better description of what it is you need. And - most important of all - do not make mistakes.

Thanks.

Best
Darek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors