Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

(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 b...
Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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's avatar
    Anonymous
    3 years ago

    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's avatar
ImkeF
Community Champion
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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