Forum Discussion
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
- Anonymous5 years ago
waeltken
Please check the steps in the 'Applied Steps' area in Query editor to find which step is the problem.Reference:
Cyclic reference in Power Query - How to avoid? - Microsoft Tech Community
Best Regards
Paul
1 Reply
- AnonymousNot applicable
waeltken
Please check the steps in the 'Applied Steps' area in Query editor to find which step is the problem.Reference:
Cyclic reference in Power Query - How to avoid? - Microsoft Tech Community
Best Regards
Paul