Forum Discussion

Melissa_Connor's avatar
Melissa_Connor
New Member
5 years ago
Solved

SUMIFS in PowerQuery

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 ar...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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.