Page 1 of 1

List.Range & List.Sum M Formulas

Posted: Sun Jun 16, 2019 3:12 am
by Jonny1010101010
Hi all,

If someone is familiar with the correct way to use these two formulas as I'm having some difficult. I used the following formula (successfully!) to create a running total calculated column (of time stored as a decimal number):

=List.Sum(List.Range(#Added Index"[TimePerCase],0,[Index]))

However, replicating the conditions & the formula to another situation has made the formula come back with Error. I've checked this isn't to do with the data type I am referring to in each case.

The error message reads that [TimePerCase] is not a 'list' data type. However, the values in both situations are effectively the same. The [TimePerCase] are small decimals around 0.00001. The other workbook interpreted these values as a 'list'.

Does anyone know what might be causing my error? Thanks :)

EDIT: Does 'List' have a maximum number of records? My current PQ is around 16k rows.

EDIT: Another idea... I believe lists need to start with the lowest and end with the highest value in the series - if this is the case, perhaps there's no value to achieve the running total with this method with my real data (the demo file had mock data in)

Re: List.Range & List.Sum M Formulas

Posted: Sun Jun 16, 2019 4:28 pm
by Jonny1010101010
Hi all,

I've realised why the M formula works in one situation and not the other, but I'm not sure of the technical logical as to why this is - if anyone can enlighten me that would be a great help!

=List.Sum(List.Range(#"Added Index"[TimPerCase],0,[Index]))

The section in bold is meant to 'call' the previous step in the M code, and I either changed the description or had the Add Index step earlier in the code, hence why it broke the formula.

Ive never used another formula which needed a 'call', can someone explain the rationale in this situation?

Thanks :)

Re: List.Range & List.Sum M Formulas

Posted: Sun Jun 16, 2019 9:16 pm
by PhilC
I could not find a good explanation with a short search, but you need to think of M, the Power Query language as a proper programming language.

Each step is evaluated based on the reference to another step, but that step does not need to be the immediately preceding one, it could be any of the other steps. It is not a stand alone formula, rather a series of instructions building on each other.

Hope that helps.

Re: List.Range & List.Sum M Formulas

Posted: Mon Jun 17, 2019 9:00 pm
by PhilC
Just came across this while researching something else and thought it might add to the understanding of M and hw it processes.

See the latter comment from Marcel - https://community.powerbi.com/t5/Deskto ... d-p/314686

Cheers
Phil