Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
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"})
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.