Recursive functions in dataflows

[Anonymous]

Hi,

Has anyone tried writing recursive functions in dataflows. I was just moving a dataset into a dataflow and what works fine as a dataset complains about cyclic reference being created.

Recursive functions are quite handy when dealing with hierarchical data structures so would be a shame if they don't work in data flows.

[luisrh]

Are you doing this in M?    This is a functional language and the issue may be related to tail code optimization that most functional languages do.   I don't know whether the M compiler does that,  but more than likely the answer is somewhere in how this works.     Perhaps you can use functions and external code exposed to do some of this.   We have had to do that in some of our use cases.

Good luck.

[Anonymous]

Hi Yep doing it in M with functions. These are working fine in Power BI desktop and when published to the service as a dataset. But in data flows just no luck. Maybe I need to rewrite the functions.

Thanks!

[cpeavyhouse · Accepted solution]

I Struggled with this for a while as well.  It seems the solution is as simple as using an @ sign.

This is an example my coworker wrote.

let
  f = (n as number) as number => if n = 0 then 0 else if n = 1 then 1 else @f(n - 2) + @f(n - 1)
in
  f

I've tested it and it works in dataflows.  Note, when you make the recusive call, be sure to reference the name of the function from inside the let.  For example, say the external name for this is 'fib', you need to make the recursive call with @f(..).  I hope this helps!

Edit:

This is addressed on page 28 of the reference manual.

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification

[RobertSlattery]

Assigning the function to a "variable" in the current "let scope" certainly fixed it for me.  If I use the "@" in the Dataflow editor, it throws a warning, which disapears and everything works fine in the editor, if I remove the "@".  But, then the refresh fails.  Adding the "@" back in and ignoring the warning in the editor fixed the problem.

[Anonymous]

Thanks for that. Very handy. I shall go and have a look at the reference manual and find out what the @ sign is all about. Not comes across that before.