PQ - How many Scores of 80 or more have been obtained by Subject - with Pivot Table

[sandy666]

Because in the Standard Pivot Table it is not possible to use a filter of greater, less, greater or equal etc. then Power Query solves this problem

Source				Filter		Result
Name	Subject	Score		Filter		Subject	Count of Score
Jack	Maths	84		75		Chem	2
Fred	Chem	73				English	3
Pete	English	89				French	3
Harry	French	88				Maths	2
Mary	Maths	60				Grand Total	10
Sally	Chem	62
Don	English	79
Ken	French	91
Eddie	Maths	70
Sara	Chem	92
Joel	English	75
Tom	French	88
Jake	Maths	94
Noel	Chem	95

let
    def = Excel.CurrentWorkbook(){[Name="Table5"]}[Content][Filter]{0},
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    Filter = Table.SelectRows(Source, if def = null then each [Score] >= 0 else each [Score] >= def)
in
    Filter

Filter Result Filter Subject Count of Score 90 Chem 2 French 1 Maths 1 Grand Total 4

Filter Result Filter Subject Count of Score 80 Chem 2 English 1 French 3 Maths 2 Grand Total 8

Filter Result Filter Subject Count of Score Chem 4 English 3 French 3 Maths 4 Grand Total 14