Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
@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
@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