Excel Forum

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

Femco Onuch

Member

Members

Forum Posts: 35

Member Since:
May 6, 2022

Offline

June 18, 2022 - 4:25 pm

Good day forum members. I have task, which i have sought for solutions, in other forums,buh i need an efficient method.

The challenge is, 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, in this case, instead of manually removing subject scores, which to me will be a huge task and a burden .

However, the problem will this method, is which column values corresponding to that "StudentID" should be removed. Should i create a list of accredited subjects for each student? so that each student cant take more than the required. I dont know how to go about this.

This is the link to the challenge file: https://1drv.ms/x/s!Akd5adcSw6.....T?e=cip5du

Please note: The column Count uses a formula to count "text" items in the row, to determine number of subjects written.

The camth, exammth,mth,mthgrade are test, exam , total for (test+exam), and lettered grade for that subject mathematics,(mthgrade) respectively, same also for other columns, with crs, eng.

Thank you for anticipated solutions

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 18, 2022 - 5:17 pm

Hi Femco,

If the subjects vary a lot between subject, makes sense to set the allowed subjects by student.

If not, then you can set a subject priority/importance, then in PQ we can preserve only the first 2 responses corresponding to the top 2 subjects.

Or, you can set the 2 subjects list per Class.

Femco Onuch

Member

Members

Forum Posts: 35

Member Since:
May 6, 2022

Offline

June 18, 2022 - 6:23 pm

HI @Catalin,

How do i achieve this . How to set priority, subject list etc. please help!

The goal is to force each student take 2 required subjects....i am using 3 subjects in all for demo purpose, buh in reality, it may be not more than 9 subjects.

So how do i make accreditation for subjects?

following the link above:

say; ID:1002 accredited to offer Mathematic, CRS, but not English => OK since Count column doesnt exceed 2
ID: 1003 accredited to offer Mathematics, English but not CRS=> not ok buh replaced CRS for English, Count Column doesnt exceed 2, good buh offering wrong subjects combination.
ID:1007 accredited to offer Mathematics, English, but not CRS=> Not ok, since Count Column has exceeded 2, and Subject written is more than accredited.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 18, 2022 - 10:26 pm

First you have to decide what you need then we will setup a table similar to one of the tables below.

Which one fits: setting a list of subjects per group (Class), or per student?

settings per group
SS1	math
SS1	crs
SS2	eng
SS2	math
SS3	crs
SS3	eng

settings per student
1001	math
1001	crs
1002	eng
1002	math
1003	crs
1003	eng

Femco Onuch

Member

Members

Forum Posts: 35

Member Since:
May 6, 2022

Offline

June 19, 2022 - 5:10 am

@Catalin, i will prefer the latter..Setting per Student.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

June 20, 2022 - 3:02 pm

Hi Femco,

Here is an example, note the Transformation step that clears the 3 grade columns if the subject is not in the list of Accredited subjects from settings:

Transformation = Table.FromRecords(Table.TransformRows(#"Merged Queries", (row)=> Record.TransformFields(row, {
{"mthgrade", each if List.Contains(row[Accredited][Accredited],"mth") then row[mthgrade] else null},
{"enggrade", each if List.Contains(row[Accredited][Accredited],"eng") then row[enggrade] else null},
{"gradecrs", each if List.Contains(row[Accredited][Accredited],"crs") then row[gradecrs] else null}
} ))),

You can modify as many columns you need to.

Keep in mind that all students must be found in settings. The ones not in settings will have their grades cleared.