Forum Discussion
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...
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.
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.
Thank you! I had to modify this slightly to add an and but it worked perfectly, thank you so much!