Total counts of columns values exceeds set target remove values dynamically in Powerquery-CodePudding

I have a huge dataset, this data set are students records frequently coming from the web.The column "count" as shown in the image below, will count how many subjects written by each student. As a rule the subjects count shouldnt exceed "2" buh some students have erroneously or deliberately written 3 and more. Please, how can i dynamically remove some selected column values corresponding to a particular StudentID to make the "count" column read 2 instead of manually removing subject scores, which to me will be a huge task and a burden in powerquery

CodePudding user response：

See if this works for you

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
x=  Table.ReplaceValue(Source,  each [cacrs], each if [Count]>2 then null else [cacrs] ,Replacer.ReplaceValue,{"cacrs"}),
x2= Table.ReplaceValue(x,  each [examcrs], each if [Count]>2 then null else [cacrs] ,Replacer.ReplaceValue,{"examcrs"}),
x3= Table.ReplaceValue(x2,  each [crs], each if [Count]>2 then null else [cacrs] ,Replacer.ReplaceValue,{"crs"})
in x3

it erases the values in the cacrs, examcrs and crs columns if Count on that row >2