Search code examples
excelgroupingpowerquery

Power query calculate Percentage of changes in marks

I have a student data set in my excel. Let me show the data set here. I have n number of schools and marks obtained by the school in various years.

enter image description here

See, In the above data set, I want to calculate the Total marks percentage of changes over the previous two years.

Percentage of changes = (Current Year Total - Previous Year Total) / Previous Year Total * 100

Look at School S1, It doesn't have a Chemistry subject in 2020 but it is available in 2021. So When calculating the Current Year total I would like to avoid Chemistry from 2021. Its like Apple to Apple comparison. Also for 2020, Since there is no 2019 data available, the percentage of changes should be 0 or null for 2020.

I am expecting the following output,

enter image description here

How to do this in Power Query M language?

Solution

  • 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"

    enter image description here