List.Range & List.Sum M Formulas

Jonny1010101010
Posts: 16
Joined: Wed Oct 17, 2018 5:23 am

List.Range & List.Sum M Formulas

Postby Jonny1010101010 » Sun Jun 16, 2019 3:12 am

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)

Jonny1010101010
Posts: 16
Joined: Wed Oct 17, 2018 5:23 am

Re: List.Range & List.Sum M Formulas

Postby Jonny1010101010 » Sun Jun 16, 2019 4:28 pm

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

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: List.Range & List.Sum M Formulas

Postby PhilC » Sun Jun 16, 2019 9:16 pm

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.

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: List.Range & List.Sum M Formulas

Postby PhilC » Mon Jun 17, 2019 9:00 pm

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


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron