Forum Discussion
SUMIF using M Query
Hi, in below table, I would like to calculate Sum of Qty based on Date with additional column created.
I've tried using below M Query but error pop up.
Please refer Desired Outcome table for details:
| Date | Factory | Qty |
| 10/01/2023 | Ph1 | 139 |
| 11/01/2023 | Ph1 | 28 |
| 08/02/2023 | Ph2-1 | 51 |
| 23/02/2023 | Ph1 | 1 |
| 27/02/2023 | Ph2-1 | 28 |
| 10/03/2023 | Ph1 | 39 |
| 20/03/2023 | Ph1 | 37 |
| 23/03/2023 | Ph1 | 2 |
| 23/03/2023 | Ph2-1 | 1 |
| 29/03/2023 | Ph1 | 3 |
| 29/03/2023 | Ph2-1 | 1 |
| 12/04/2023 | Ph2-1 | 1 |
Below M Query was used but failed.
[CurID = [Date],
res = List.Sum(Table.SelectRows(#"Changed Type", each [Date] = CurID) [Qty])]
[res]
Desire Outcome with "Sum of Qty" column created:
| Date | Factory | Qty | Sum of Qty |
| 10/01/2023 | Ph1 | 139 | 139 |
| 11/01/2023 | Ph1 | 28 | 28 |
| 08/02/2023 | Ph2-1 | 51 | 51 |
| 23/02/2023 | Ph1 | 1 | 1 |
| 27/02/2023 | Ph2-1 | 28 | 28 |
| 10/03/2023 | Ph1 | 39 | 39 |
| 20/03/2023 | Ph1 | 37 | 37 |
| 23/03/2023 | Ph1 | 2 | 3 |
| 23/03/2023 | Ph2-1 | 1 | 3 |
| 29/03/2023 | Ph1 | 3 | 4 |
| 29/03/2023 | Ph2-1 | 1 | 4 |
| 12/04/2023 | Ph2-1 | 1 | 1 |
Thank you.
Tan LC
3 Replies
Replies have been turned off for this discussion
Hi, Tan_LC
groups = Table.Group(before, {"Date"}, {{"rows", each _, type table [Date=nullable date, Factory=nullable text, Qty=nullable number]}, {"Sum of Qty", each List.Sum([Qty]), type nullable number}}), after = Table.ExpandTableColumn(groups, "rows", {"Factory", "Qty"}, {"Factory", "Qty"})NewStep=let a=Table.Group(#"Changed Type","Date",{"n",each List.Sum([Qty])}) in Table.AddColumn(#"Changed Type","Sum of Qty",each a{[Date=[Date]]}[n])
AlienSx wdx223_Daniel , possible to attach me the solution in pbix. format coz I've tried inserting both of your formulas but still error.
Thank you.
Tan LC
