How to get the top 3 and bottom 3 projects for each team scale and display in a Cluster Map?

Question

I have a table with 3 columns namely Team Scale(1 to 4), Project, Total Score. I want to display a Cluster Map for each Team Scale with the top 3 and bottom 3 projects. Can someone help me out with detailed steps or share the pbix with me? Tried out using different formulae didn't get what was expected.

Answer 1

Is this what you are looking for?

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Team Scale"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Total Score", Order.Ascending}}),3) & Table.LastN(Table.Sort(_,{{"Total Score", Order.Ascending}}),3), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Project", "Total Score"}, {"Project", "Total Score"})
in  #"Expanded data"