Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
fisrtCallAmount = "40000",
callAPI = (url as any) as record =>
let
...
source = Json.Document(Web.Contents("https://mri_api", [RelativePath=relPath])),
results = try source[value] otherwise null,
nextURL = try source[nextLink] otherwise null,
cleanedURL =
if odata = null
then nextURL
else Replacer.ReplaceText(nextURL, "%24", "$"),
return = [results=results, next=cleanedURL]
in
return,
The above query is used in a list generator to get all the data from the database, from MRI using their API service Looking for help connecting to MRI SaaS data source, a few records at a time. In this case the page size is 40000.
This query works... sometimes. It is suppose to pull 450k+ records but it just pulls some factor of 40000, in other words at some point 'source' gets a null value, where it should be getting data, and ends the list generation. There are also times where the query would fail on the first call
I am unable to see if MRI is indeed sending the data through for those calls or if its Power BI thats cutting out.
Decreasing the page size has variable results. Im currently testing with 10000 but a successful run takes short of 50min where 40000 takes just over 20min So I would prefer to use 40000.
I have tried this in both pbi desktop and dataflows.
I have also tried doing a bunch of retries:
source_1 = Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath])),
source_2 = if try source_1[value] otherwise null = null
then Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath]))
else source_1,
source_3 = if try source_2[value] otherwise null = null
then Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath]))
else source_2,
source = source_3,
This just returns an empty list each time and I couldnt be bothered to figure out why this isn't working.
So I then tried to clean this up to get the below query. only to get a cyclic refernence error.
let
maxCalls = 5,
getSource = (counter as number, relPath as any) as any =>
let
source = Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath])),
srcValue = try source[value] otherwise null,
return = if srcValue = null and counter < maxCalls
then getSource(counter +1, relPath)
else source
in
return
in
getSource
BUT the below recusrive call works. 🤷🏼 using '@' didnt get rid of the error for me.
getSource = () =>
let
Source = try #"API Function"("method_name") otherwise null,
return = if Source = null then getSource() else Source
in
return,
I also tried the Function.InvokeAfter and increased the timeout on the Web.Contents to avail.
Has anyone else come accross this issue? or know how to resolve it.
Hi @GerhardA
For debugging purposes, change your function to return the entirety of source so you can see what the API returns when it stops/fails.
As it is when source[value] doesn't exist, you end up with null, but that doesn't mean that nothing at all was sent by the API, it should be returning somethign for each call.
Also, what is odata an where is its value set?
Regards
Phil
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.