=if [Valores] <> null then try List.First(List.RemoveNulls(List.LastN(#"Added Index"[Valores],List.Count(#"Added Index"[Valores])-[Index]))) - [Valores] otherwise null else null
The above array formula will return a #VALUE! error in N94.maybe:array-entered in cell L6 and copied down?Code:=IF(COUNT(J6),INDEX($J$1:$J$99,MIN(IF((ISBLANK($J7:$J$99)),FALSE,ROW($J7:$J$99))))-J6,"")
As I am still discovering list functions - I do like them a lot - I fail to understand how some work though, thus fail to use the most performant one. I tried to use List.Transform(Many) but got nowhere.There are multiple avenues that you can take within PQ to arrive at the solution.
...
Another is to use List functions to perform calculation (One advantage of this method is speed of data refresh over above method).
...
...Code:=if [Valores] <> null then try List.First(List.RemoveNulls(List.LastN(#"Added Index"[Valores],List.Count(#"Added Index"[Valores])-[Index]))) - [Valores] otherwise null else null
I also do use "connections only" a lot for the same reason, @Peter Bartholomew , and I am by far an expert in the matter. If the result is there via the UI, at least it can be easily understood by many users. For sure, it is not always the most optimal way. Often I cannot say which option would be more efficient. Trial and error is my game then.Hi Guido
Inspired by you I tried a PQ solution but tried to keep it simple in order to stay within my understanding. It worked but I tend to produce 'connection only' queries that are merely steps within an existing query that I will wish to reuse. I cannot help but think that this is simply the product of my failure to reference anything but the immediately previous step whilst building a query. Is there a better way?
List.RemoveNulls(List.Transform(LstValores, each _ - (List.Last(List.FirstN(LstValores,(List.PositionOf(LstValores, _ ) ) ) ) ) ) )