Creating a Recursive Function - Level Hard ? x = previous Y and Y = (a+x)/e

[felipecarvalho]

Hello, Everybody

I am stuck with a problem for several hours ( 20+ and counting...)

I post the original problem on the link below this topic, but I think i narrow it down to a very specific part ( so I am not just repeating the same question which I know is not recommended)

I create a function and I want to use the result of the previous "resultadoatual " as the value of "pmanterior " , because I need this value to calculate the correct "resultadoatual " 

I did a simple example with just a few values and I get the expected result... But not with my real values.

This is the code:

In the first iteration "pmanterior " is equal resultadoant that is = to 0 when I call the function).

The first time I get the expected result for "resultadoatual ". 

In the second iteration, the value of "pmanterior " should be the "resultadoatual " of the previous iteration.   Then I can use this value to calculate the value of "resultadoatual " of the current iteration.

For some reason, it is not working(i get the wrong results). Maybe I don't understand the order in which the iterations and values are replaced in the expressions...

Can somebody help?

I put the code of the image here:

(listaoper as list,listaestoque as list,listaquantidadenota as list,listapmnota as list, contador as number,resultado as number,resultadoant as number,listaestoqueanterior as list)=>

let         

contadoratual = contador -1, 

estoque =listaestoque{contador},

estoqueanterior = listaestoqueanterior{contador},

operacao = listaoper{contador},

quantidade = listaquantidadenota{contador},

pmnota = listapmnota{contador},

valor = if operacao = "C"

           then (quantidade * pmnota) 

           else pmanterior,

pmanterior = resultadoant 

resultadoatual =                     

                      (valor+pmanterior) /estoque ,

clausula = 

  if contadoratual<=-1 

   then resultadoatual

 // resultadoatual

    else @loopPM(listaoper,listaestoque,listaquantidadenota,listapmnota,contadoratual,resultadoatual,resultadoatual,listaestoqueanterior) 

in resultadoatual

------------------------------------------------------------

This is the link that I refer to that had the "original" question and some dummy data. It gives some context and explains the expected result, but the data is very "raw"... I think if I could find the answer to the question above I will resolve the original question.

https://community.powerbi.com/t5/Power-Query/Stock-Portfolio-Control-Can-i-use-the-column-that-I-am-...

[felipecarvalho]

@Greg_Deckler @v-kelly-msft 

Another dawn with no progress...

Starting to get very frustrated, because I see all the comments that you both have made, make sense, but when I am trying I can't get the expected results...

On my problem, I have to pass a lot of different changing values.

On the examples, usually is only one number changing and the rest is fixed...

I am considering paying someone to get past this hurdle.. anybody interested?

[felipecarvalho]

@Greg_Deckler 

@v-kelly-msft 

Thank you both for your times.

 

I had someone point me at the direction of the List.Generate. 

But was about the same time I discover how to do the loop with function and about recursive function.

From what I understand  my problem can be solved in both ways :  

1) Using List.Generate

2) Using recursive function 

Do you agree? Or do I have to combine both?

I had already watched that video, but I found it a little bit complicated ( the function itself is ).. and since I was already involved in the second method I decided to try a little bit more this way, before starting all over.

I will keep trying this next few days. Gona read again all the previous messages and see if I can make it work.

I was reading the documentation and found this explanation that maybe can help me with the solution:

"

List and record member expressions (as well as let expressions, introduced further below) are evaluated using lazy evaluation, which means that they are evaluated only as needed. All other expressions are evaluated using eager evaluation, which means that they are evaluated immediately when encountered during the evaluation process. A good way to think about this is to remember that evaluating a list or record expression will return a list or record value that itself remembers how its list items or record fields need to be computed, when requested (by lookup or index operators). "

Again, thank you all for the help!

[Greg_Deckler]

@felipecarvalho I can't speak to List.Generate as I have never done it that way. But @v-kelly-msft is generally pretty reliable so if they say it is so I would tend to believe them! 🙂

[Greg_Deckler]

@felipecarvalho This was my experience in working with recursion in Power Query.

Fun with Graphing in Power BI - Part SQRT(POWER(SQ... - Microsoft Power BI Community

[felipecarvalho]

@Greg_Deckler  

First of all thanks for your reply!

I read your article, but unfortunately, I still wasn't able to solve the problem ( maybe it's because I am not a mathematician, or an engineer ... and certainly not a pretty girl (pun intended only for those who read the link that Greg provide).

It would be very selfish of me to ask to you look at my code and point me in the right direction?
Just a little correction on the last part the "final" code was :

" This is the loop counter that says how my loops the function has to do to get the correct result for the specific line in my table ( i call this function on a custom column context)

clausula =
if contadoratual<=-1
then resultadoatual

else @loopPM(listaoper,listaestoque,listaquantidadenota,listapmnota,contadoratual,resultadoatual,resultadoatual,listaestoqueanterior)

in clausula

" ( in my previous post I wrote " in resultadoatual" but that was just me debugging..)

Maybe with some specific questions, I can get to the answer myself..:

1) From what I understand you are saying that I need to create 2 separate functions? Can I do that even if the result from each function needs the value from the other?

2) I could not find an if statement on your code to count the number of loops that the function is called. Can you enlight me?

3) I notice that you use the name "list" as a variable ?
list = {Text.From(startX) & "," & Text.From(startY)},
I thought that this was a reserved name. Any special reason that you did that?
Just this part gave the idea of creating a variable list to store all the "resultadoatual" ( read CurrentOutput) and then call the previous one in the calculations, but could not make that work...

4) Is there any problem that I am trying to use the function in a table on a custom column context??

I think I am missing a lot of knowledge of how the loops works..

In this simple example it appear to get the desire result

The simple code that I am referring to is this :

Thanks for all the help!!!

[v-kelly-msft]

Hi @felipecarvalho ,

You could use List.Generate() and create a function to realize it,check the vedio below:

https://www.youtube.com/watch?v=mXbjoSg9ldo

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Greg_Deckler]

@felipecarvalho OK, let me see if I can do a deeper dive to explain my code.

fnSierpinski3Init

let
   fnSierpinski3Init = (start as text,counter) =>
   let
       startX = Number.FromText(Text.BeforeDelimiter(start,",")),
       startY = Number.FromText(Text.AfterDelimiter(start,",")),
       list = {Text.From(startX) & "," & Text.From(startY)},
       Return = fnSierpinski3(-1,0,1,0,0,2,list,counter)
   in
       Return
in
   fnSierpinski3Init

OK, the name list for the variable was just random because it was a list. It could be any name. I am using a list variable because I need to store the interim values generated during recursion so I store them in the list variable. Yes, I created this initialization function separate in order to keep the code cleaner. Once I have everything initialized, I then call the recursion function with a set number of iterations, which is the "counter" variable.

fnSierpinski3

let
   fnSierpinski3 = (vert1x,vert1y,vert2x,vert2y,vert3x,vert3y,list,counter) =>
   let
       origin = List.Last(list),
       originX = Number.FromText(Text.BeforeDelimiter(origin,",")),
       originY = Number.FromText(Text.AfterDelimiter(origin,",")),
       randomVert = Number.IntegerDivide(Number.RandomBetween(1,4),1),
       targetVertX = if randomVert = 1 then vert1x else if randomVert = 2 then vert2x else vert3x,
       targetVertY = if randomVert = 1 then vert1y else if randomVert = 2 then vert2y else vert3y,
       plotX = (originX + targetVertX)/2,
       plotY = (originY + targetVertY)/2,
       end = {Text.From(plotX) & "," & Text.From(plotY)},
       list1 = list & end,
       Return = if counter = 0 then list else fnSierpinski3(vert1x,vert1y,vert2x,vert2y,vert3x,vert3y,list1,counter-1)
in
       Return
in
   fnSierpinski3

You can ignore pretty much everything in here that doesn't reference list and counter variables, which as you can see are being passed into the function. I use List.Last to grab the previous value (the value from the last iteration) and this is used as the seed for the new iteration. The list1 line adds the latest iteration's value to the end of the list. The very next line is the if statement you are looking for. if my counter reaches 0, I am done recursing so I return the list variable (which now that I am re-reading this I should have returned list1). If counter is not zero then I recursively call the function again but feed it list1 (which has my latest iteration tacked onto the list) and counter-1 so that I decrement my counter and don't end up in an infinite loop.

So with these two functions, I then just have to convert the list to a table and do a little renaming of columns, etc. The full query looks like this:

let
   Source = fnSierpinski3Init("0,0",3000),
   #"Converted to Table" = Table.FromList(Source, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
   #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type number}, {"Column2", type number}}),
   #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "x"}, {"Column2", "y"}}),
   #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
   #"Added Custom" = Table.AddColumn(#"Added Index", "Color", each if [x]<0 and [y]<1 then 0 else if [x]>0 and [y]<1 then 1 else 2)
in
   #"Added Custom"

So, this is a separate query from the functions. I call my initialization function which starts the recursion. Coming back from this line I then have to convert the list being returned into a table. This is the second line of the code "Converted to Table". The rest of the lines are irrelevant. 

Hopefullly that helps!?!?