M-Code Running Totals

Hello,

I am seeking for help regarding running totals as I am unable to create them my data is slightly different and it does not work as per video from YouTube from:

or

I would appreciate if you help me to resolve two slightly different scenarios:

  1. The data is stored in Table, I have manage to sort data by date and add Index Column to the table using the below m-code:

Table.Sort([All Rows],{{“TransactionDate”,Order.Ascending}}),
Table.AddIndexColumn([SortedTable], “Index”, 1, 1, Int64.Type)
image

After that I expand all the data, there are a lot of different columns but the only needed for tunning totals are as per below ChequeValue and Index:

For running Totals I tried the below formula:
List.Sum(List.FirstN(“Index”[ChequeCurrency],[Index]))

but I get an error:
“Expression.Error: We cannot apply field access to the type Text.
Details:
Value=Index
Key=ChequeCurrency”

And I am not sure how to fix it.

  1. Approach I used the code as per described in the below yt:

(funcition):


But the formula/fuction does not work correctly as the Custom Table is adding the whole index as one total so for one table it could be 0, for other it could be 43,21 and other 5892 whereas it should technically work as running total but it does not.

Instead of using the fiction as per YT I tried to add on my own cumulative total to each table with the below mcode:

Table.AddColumn([SortedTable], “RunningTotal”, each List.Sum(List.FirstN(“SortedTable”[ChequeValue], [Index])), Int64.Type)

but I get an error
image
Expression.Error: We cannot apply field access to the type Text.
Details:
Value=SortedTable
Key=ChequeValue

Could you please advise??
@BrianJulius

HI @Matty,

Please don’t mention anyone name in the forum posting as it will discourage others in helping you.

Can you please provide the dummy excel file /power bi file so people of the forum can provide a solution for you?

Did you try using the DataMetor to help you with a solution?

Thanks
Keith

Hi @Matty,

By the looks of it you need/want to create grouped running totals, review this.

As for the error you’re experiencing on, “Index”[ChequeCurrency] and “SortedTable”[ChequeValue] respectively, values between quotes are strings unless preceded by a # then it refers to an identifier (query or step).

I hope this is helpful