• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Total counts of columns values exceeds set target remove values dynamically in Powerquery|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Total counts of columns values exceeds set target remove values dynamically in Powerquery|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryTotal counts of columns values exce…
sp_PrintTopic sp_TopicIcon
Total counts of columns values exceeds set target remove values dynamically in Powerquery
Avatar
Femco Onuch
Member
Members
Level 0
Forum Posts: 21
Member Since:
May 6, 2022
sp_UserOfflineSmall Offline
1
June 18, 2022 - 4:25 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
June 18, 2022 - 5:17 pm
sp_Permalink sp_Print

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.

Avatar
Femco Onuch
Member
Members
Level 0
Forum Posts: 21
Member Since:
May 6, 2022
sp_UserOfflineSmall Offline
3
June 18, 2022 - 6:23 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
June 18, 2022 - 10:26 pm
sp_Permalink sp_Print sp_EditHistory

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
Avatar
Femco Onuch
Member
Members
Level 0
Forum Posts: 21
Member Since:
May 6, 2022
sp_UserOfflineSmall Offline
5
June 19, 2022 - 5:10 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
June 20, 2022 - 3:02 pm
sp_Permalink sp_Print sp_EditHistory

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
Femco Onuch
Member
Members
Level 0
Forum Posts: 21
Member Since:
May 6, 2022
sp_UserOfflineSmall Offline
7
June 20, 2022 - 11:43 pm
sp_Permalink sp_Print sp_EditHistory

Thank you for taking your time to providing a solution to this,  i will go through it later, then click the correct mark symbol.

Avatar
Femco Onuch
Member
Members
Level 0
Forum Posts: 21
Member Since:
May 6, 2022
sp_UserOfflineSmall Offline
8
June 21, 2022 - 11:25 pm
sp_Permalink sp_Print

@Catalin, You are blessed! Thank you!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Alan Sidman, Ngoc Tinh, Dieneba NDIAYE, Alexandra Radu, Natasha Smith, Monique Roussouw
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.