0

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.

meow
  • 33
  • 6

1 Answers1

0

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"
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Not really. For an example for Team scale 1 the top 3 projects are A, E and L. Like that. – meow Mar 15 '23 at 12:42
  • I accidentally had it set for top bottom 4, now code change for top/bottom 3. If you want just the top and not bottom, then edit the code to remove the Table.FirstN() part. Does this code generate what you want when it is run on your data set which I don't want to retype? – horseyride Mar 15 '23 at 13:26
  • It throws an error 'Expression.Error: We couldn't find an Excel table named 'Table1'. But this is the name of my table tho. What could be the reason? – meow Mar 15 '23 at 14:58
  • Sorry, thats not something I can answer for you. IF you load your source table into powerquery and look at the code in home...advanced editor... it will show you the name it thinks your data is, which may not be the same as you think it is – horseyride Mar 15 '23 at 16:00