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


SourceFilterResult

NameSubjectScoreFilterSubjectCount of Score

JackMaths
84
75Chem
2

FredChem
73English
3

PeteEnglish
89French
3

HarryFrench
88Maths
2

MaryMaths
60Grand Total
10

SallyChem
62

DonEnglish
79

KenFrench
91

EddieMaths
70

SaraChem
92

JoelEnglish
75

TomFrench
88

JakeMaths
94

NoelChem
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




FilterResult

FilterSubjectCount of Score


90Chem
2

French
1

Maths
1

Grand Total
4




FilterResult

FilterSubjectCount of Score


80Chem
2

English
1

French
3

Maths
2

Grand Total
8




FilterResult

FilterSubjectCount of Score


Chem
4

English
3

French
3

Maths
4

Grand Total
14