Forum Discussion

waeltken's avatar
waeltken
Helper I
5 years ago
Solved

How to diagnose source of cyclic reference error in Power Query Custom Function

Hey PBI Universe,

 

I'm encountering a cyclical reference error in a custom function meant to loop through paginated API responses and to return an output via List.Combine of all daisy chained calls API responses. 

 

When I call the custom function with default starting parameters instead of a table I keep getting the error message: "An error occurred in the ‘’ query. Expression.Error: A cyclic reference was encountered during evaluation."

 

Here is the M code (API key is obscured)

 

(optional nextflag as logical, optional nexttoken as text, optional loop as number, optional paginateddata as list) =>
let
Source = Json.Document(Web.Contents("https://api.lever.co/v1/requisitions ?",
[
RelativePath=if loop = 0
then null
else "offset=" & nexttoken,
Headers=[Authorization="Basic INSERT API KEY"]
]
)),
nextflag = Source[hasNext],
nexttoken = Source[next],
currentData = Source[data],
appendedData = List.Combine({paginateddata, currentData}),
loopNum = loop + 1,
output =
if nextflag = false or loopNum > 100
then appendedData
else @#"GET Lever Reqs"(nextflag, nexttoken, loopNum, appendedData)
in
output

 

Here are my starting parameters when I invoke the function:

 

= #"GET Lever Reqs"(true, null, 0, { } )

 

nextflag = true

nexttoken = null

loop = 0

paginateddata = { }

 

 

In an earlier version of the custom function it seemd to work somewhat, but essentially just cycled through 100 tries and returned the same 50 default records, in other words the pagination parameters were not being passed appropriately back to the API endpoint and the function just received 100 times the same response and it succefully combined those results. So I know that it works in general.

 

The issue is that the error only show us in the function results after the function has been invoked with no way to pinpoint where in the code the cyclical reference is. Is there a better way to debug M code? Is there a feature in PBI or Power Query which allows the user to step through what happenes during any one step?

 

Thank you for any pointers you can provide.

 

Regards,

 

 

Henrik