Same Power Query on different Sheets and need a total from same tables for the work book

Hi you all very clever excel people.

I consider myself average to above average at excel, but this has me stumped.

I am a football (soccer) referee and i recently tried to collate all my data in a excel workbook. Each season is a new worksheet and all the worksheets have the same formulas and tables and power query's.

As you can see I have 5 tables which each have there own power query, per sheet, so that 30+ queries (as each sheet has 5 queries)?

Is there a way where i can have only 5 power queries for different data on different sheets and for it to be dynamic. So basically I only have 5 power queries instead of the 30+ i have now.
Also is there a way where I can add up all these tables in a "career worksheet" with all data extracted from each table and into their own relevant table as a complete total, and this to be dynamic. As each new season will require a fresh new worksheet.

My tables are in columns CN2 onwards and power queries are Z&AA, AC&AD, AF,AG&AH, AJ,AK,AL.

Also its worth noted I'm running excel2016 version so do not have the ability on the newer functions like UNIQUE etc

I've tried to load the whole workbook but its over the requirements of 1000kb that this site allows, so i've posted an example page so you can get the jist, but without the power query, if you need the whole book let me know.

Any help would be greatly apricated.

Thanks for taking a look

Are you still using Excel 2003? If not, please update your profile!

Here are two possibilities:
On the Table2 sheet are regular pivot tables produced from the green/white table which itself is produced from the following power query code:

Please Login or Register  to view this content.

Note that the Win column was added later using: =AND(AGGREGATE(14,6,[Score]/([Date]=[@Date]),1)<>AGGREGATE(14,6,[Score]/([Date]=[@Date]),2),AGGREGATE(14,6,[Score]/([Date]=[@Date]),1)=[@Score])

A second option would be to load the green/white table into the data model, add measures that would do the calculations done using the getpivotdata function on the Table2 sheet, and produce power pivot tables.
Note that in either case the pivot tables may be filtered using the Year slicer.

Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.