Get Capped Maximum Value From List

Question

I have a list of values that range anywhere from 500-1000. I have a second list of values that denote relevant breakpoints in the 500-1000 range (500, 520, 540, 600, etc). I need to return the highest value in the second list that is less than the value in a given number from the first list. I noticed the "N" functions let you set a conditional on them, so for example if I do:

List.Max(List.FirstN(SomeTable[Breakpoints], each _ < 530))

It correctly returns 520 to me. However if I put this inside an AddColumn function and change the 530 to a local field reference:

Table.AddColumn(MyTable, "MinValue", each List.Max(List.FirstN(SomeTable[Breakpoints], each _ < [SomeNumbers])))

Then I get a "We cannot apply field access to the type Number" error. Is what I'm trying to do possible and I'm just formatting it wrong? I always get confused with scope and references in PQ, so it may just be that.

Answer 1

After each, [SomeNumbers] by itself is short for _[SomeNumbers] (which is what you see when filtering a column). In the List.FirstN call, _ refers to a number in the list instead of a row in a table: the value of _ is tied to the closest each, where closeness is measured by the number of layers of nesting between _ and the appearance of each . Therefore, in your code [SomeNumbers] is trying to find the column SomeNumbers on a number, which doesn't exist.

There are a couple ways to fix this:

1. You can use a let...in statement to store the current value of the SomeNumbers column to use it for later, like so:

each let currentNumber = [SomeNumbers], result = List.Max(List.FirstN(SomeTable[Breakpoints], each _ < currentNumber)) in result

2. You can explicitly define a function with the (x) => ... syntax instead of using each twice, like so:

each List.Max(List.FirstN(SomeTable[Breakpoints], (point) => point < [SomeNumbers]))