With all the data loaded into Powerquery (data ... from table/range [x] headers) this seems to work if pasted into home... advanced editor... Make sure to use your table name instead of Table1, like my sample
what I am basically doing is subtracting one from the year and merging the table on top of itself to find the value from the prior year. Then if prior year total is zero (missing subject), erase the value from the current year. Then I group on Year and School and do the calculation, with the total depending on lower of the Total and the revised-Total, since different years would sum different columns
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"Year", Int64.Type}, {"Subject", type text}, {"Total", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([School] <> null)),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Prior Year", each [Year]-1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"School", "Subject", "Prior Year"}, #"Added Custom", {"School", "Subject", "Year"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Total"}, {"PriorYearTotal"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Table", "RevisedTotal", each if [PriorYearTotal]=null then 0 else [Total]),
#"Grouped Rows" = Table.Group(#"Added Custom2", {"School", "Year"}, {
{"Total", each if List.Sum([RevisedTotal])=0 then List.Sum([Total]) else if List.Sum([RevisedTotal])<List.Sum([Total]) then List.Sum([RevisedTotal]) else List.Sum([Total]), type nullable number},
{"Percent", each (List.Sum([RevisedTotal])-List.Sum([PriorYearTotal]))/List.Sum([PriorYearTotal])*100, type nullable number}})
in #"Grouped Rows"
