(SIMPLE) grouped running total

[Anonymous]

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.

[ImkeF · Accepted solution]

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])))

[ImkeF]

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.

[Anonymous · Accepted solution]

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

[ImkeF · Accepted solution]

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])))

[Anonymous]

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])))