SUMIFS in PowerQuery

[Melissa_Connor]

Hi Amazing Gurus

I am needing help to do a Sum IFS or similar in PowerQuery.

We receive applications throughout a school year to provide funding for students with specific health needs. There are a maximum number of hours/funding that we allocate per health need. Some Schools will submit multiple applications for the same student throughout the year which will eventually add up to exceed the maximum hours that we would allocate.

I need help to work out how much they have already been allocated, compared to their new application to see if they are eligible for any more hours/funding or not with their new application…… So far I have managed to collate all the applications in to one spreadsheet (love Power Query) for this. I have created a Master Spreadsheet which tracks the student’s applications, the date they applied, and hours allocated. I have been able to identify duplicates and flag them. I just can’t get my head around how to do the calculation to figure out the difference as it needs to look at so many columns and then apply a calculation:

Unique Identifier (being Student ID Code and Health Need) which flags it is a duplicate application

Original Application date (to check if it is an earlier application than the current one)

Hours already Allocated

Then calculate the difference they are entitled to if anything at all.

For example if Student X with Diabetes applied on 11/11/2020 and is receiving the Maximum allocation of 3.3hrs, then I want the new application that is received on 20/11/2020 to calculate to 0 as they are already receiving the maximum hours. You will see below (yellow highlighted row) that they are already flagged as duplicate, but the Hours allocated still calculates to 3.3, but it should be 0.

Alternatively if Student 12 with Diabetes is already receiving 1.6 hours allocation, then I want the new application that is received for student 12 to calculate to 1.7 as they are entitled to a maximum of 3.3 hours.

Appreciate your help.

[v-alq-msft · Accepted solution]

Hi, @Melissa_Connor 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a new custom column with the following m codes.

= Table.AddColumn(#"Changed Type", "Result Allocated", each let 
date=[Date],
sname = [StudentName],
tab = Table.SelectRows(#"Changed Type",each [StudentName]=sname and [Date]<date)
in 
if Table.IsEmpty(tab)
then [Hours allocated]
else
  if List.Sum(tab[Hours allocated])>=3.3 
  then 0
  else 3.3-List.Sum(tab[Hours allocated]))

 

Result:

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Melissa_Connor]

Thank you! I had to modify this slightly to add an and but it worked perfectly, thank you so much!

[CNENFRNL]

Hi, @Melissa_Connor , in general, you might want to use Table.Group to obtain the sum of certain rows.

It's way much easier for others to keep up with you over your question with some mockup dataset and simple examples of desired results.