Forum Discussion
(SIMPLE) grouped running total
I've seen a few similar threads but none in PowerQuery or simple enough for me to understand.
I have a dataset:
id | date | groupId | value |
which i would like to sort by date, then group by groupId, then calculate a runningTotal at a specific date, and the ungroup everything into a single list again.
1)Sort by date, easy
= Table.Sort(#"Geänderter Typ",{{"date", Order.Ascending}})
2)Group by groupId
= Table.Group(#"Sortierte Zeilen", {"groupId"}, {{"grouped", each _, type table [id=nullable number, date=nullable date, groupId=nullable number, value=nullable number]}})
this is also so far straight forward
3)running total, here I have defined a function fnRunningTotal:
= (ValueToRunningSum, Index) => List.Sum(List.FirstN(ValueToRunningSum, Index))
This should hopefully take the First N of a column, selected by index, and then sum over those, e.g. a running total.
For this i require an incrementing index, which i must apply per group.
= Table.AddColumn(#"Gruppierte Zeilen", "groupedWithIndex", each Table.AddIndexColumn([grouped], "Index", 0, 1, Int64.Type))
Then i must apply my function, again inside a group so i tried this:
= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal",each fnRunningTotal([value],[index])))
This results in an error in the "runningTotal" column, i assume i'm not referenceing [value] and [index] correctly (am i missing row context somehow`)
Can someone help me finish this? I'm currently not interested in the most performant solution, more in something that works, which I can understand, as I'm currently struggling with the PowerQuery Syntax and Programming style.
Hi Anonymous ,
this is probably because you are using the syntax sugar "each" twice, which creates ambiguity.
Instead, try re-writing it like so:
= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal(inner[value],inner[index])))AH such a dumb mistake, indeed the "index" should have been "Index". Thank you. Now it seems that my Running total function itself is wrong as I am receiving the error:
Error in the Query ''. Expression.Error: Der Value "1" cannot be converted to Type "List".
I suppose this makes sense as my function fnRunningTotal now is receiving two scalar values (value and index) and then running List functions on them.
= (ValueToRunningSum, Index) => List.Sum(List.FirstN(ValueToRunningSum, Index))
So i have now changed your expression to:
= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal([groupedWithIndex][value],inner[Index])))
So now i am passing the inner[Index] as the "N" in List.FirstN and and the entire value column as the "List"
Thank you for your help
4 Replies
Hi Anonymous ,
this is probably because you are using the syntax sugar "each" twice, which creates ambiguity.
Instead, try re-writing it like so:
= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal(inner[value],inner[index])))Hey Imke,
Thanks for the reply.
I've tried your solution but sadly it doesnt work, im still receiving an error on the runningTotal column:
Expression.Error: The Field"index" of the Dataset was not found.(manually translated to english)
for this expression:
= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal(inner[value],inner[index])))
Please be aware that M is case sensitive.
You can try this:
Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal(inner[value],inner[Index])))
or this:
Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal(inner[value],[Index])))
actually not sure where the Index column sits, actually.AH such a dumb mistake, indeed the "index" should have been "Index". Thank you. Now it seems that my Running total function itself is wrong as I am receiving the error:
Error in the Query ''. Expression.Error: Der Value "1" cannot be converted to Type "List".
I suppose this makes sense as my function fnRunningTotal now is receiving two scalar values (value and index) and then running List functions on them.
= (ValueToRunningSum, Index) => List.Sum(List.FirstN(ValueToRunningSum, Index))
So i have now changed your expression to:
= Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "groupedWithRunningTotal", each Table.AddColumn([groupedWithIndex],"runningTotal", (inner) => fnRunningTotal([groupedWithIndex][value],inner[Index])))
So now i am passing the inner[Index] as the "N" in List.FirstN and and the entire value column as the "List"
Thank you for your help