This may turn into a long winded Q so I apologies in advanced. I'm doing some work for a sports league & need some help with advanced Pivot Table help.

I'm trying to produce a "Historical Record" spreadsheet for the league. I am able to get my required data into a suitable pivot table (See Image 1). The pivot table has two rows these are Name & Team. The team row is used for a future section (Explained Later)

and KC can be:

=QUERY('Player Records - Offense'!$A:$H,
 "select A, max(D) where B = 'KC' group by A 
  order by max(D) desc limit 10 label max(D)''", 0)