Looping M code function for ServiceNow API

[rbreneman]

Hi!

I'm hoping someone can help me. I feel like I'm close and I've seen lots of information online about this I just can't seem to get the right combination or syntax. I have a function built and when I manually invoke the function it works. I'm limited to pulling 10,000 records in a single API transaction so I need to build another query that will loop this function until there are no more records to pull. My table query that I tried building doesn't work. It runs for ~10 seconds like maybe it pulled the first batch but then errors out. I don't know how many total records there are, so my goal is to have the first pass run with an offset of 0, the second pass run with an offset of 10000, the third pass run with an offset of 20000, etc... until there are no longer records to pull.

Any help you're able to provide would be super helpful!

Thanks so much!

My function (working):

let
Source = (Offset as number) => let
Source = Json.Document(Web.Contents("https://acmecorp.service-now.com/api/now/table/sys_user?sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_fields=user_name%2Cemail%2Csys_id%2Cname%2Cdepartment%2Ccompany&sysparm_limit=10000&sysparm_query=ORDERBYsys_created_on&sysparm_offset="&Number.ToText(Offset)))
in
Source
in
Source

 My table query (not working):

let
    Source = List.Generate( ()=> [Result= try function_user(0) otherwise null, Offset = 0], each List.Count([Result]) >1, each [Result= try function_user([Offset]) otherwise null, Offset = [Offset] +10000], each [Result])
in
    Source

[Syndicate_Admin]

Hi Are you sure this refreshes in the service? I just implemented exactly the same query and it cannot be refreshed in the service as I get this error in the data source settings "You can't schedule refresh for this dataset because the following data sources currently don't support refresh". 

My API call is also wrapped in a function and uses a relative path for all variables. I call the function in a loop so I can perform multiple API calls.

Are you able to confirm if your data source can be refreshed in the service and if the above actually worked as is?

Thanks

[hugo_barbara]

Nice!!

[rbreneman · Accepted solution]

Disregard. I found a solution!

Here is my working table query:

let
    Source = List.Generate(
        () => [Offset = 10000, x = function_user(0)],
        each not List.IsEmpty( [x][result] ),
        each [offset = [Offset] + 10000, x = function_user([Offset]) ],
        each [x]
    )
in
    Source

This video and article were a huge help: https://gorilla.bi/power-query/list-generate-api-calls/, https://www.youtube.com/watch?v=a_RJzoj1cnM.

Hopefully this helps someone else!

[njglen]

One other issue is once these are working, they cannot be auto refreshed in Power BI Service. As Service does not support Dynamic Data-sources:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

Do you come across this and if so did you resolve it. 

[rbreneman]

Hi @njglen ,

Sorry for the delay responding. I hadn't yet gotten around to publishing into the service so I was not aware, but yes, same thing occured when I tried to refresh in the service.

I was able to make the following change to my function and this should now work in the service with refreshes. Essentially leaving the base URL as the beginning of Web.Contents and then wrapping the rest of the URL into RelativePath.

let
Source = (offset as number) => let
    Source = Json.Document(Web.Contents("https://acmecorp.service-now.com", [RelativePath="/api/now/table/sys_user?sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_fields=user_name%2Cemail%2Csys_id%2Cname%2Cdepartment%2Ccompany&sysparm_limit=10000&sysparm_query=ORDERBYsys_created_on&sysparm_offset="&Number.ToText(offset)]))
in
    Source
in
    Source

[njglen]

Just a correction in table Query:

let
    Source = List.Generate(
        () => [Offset = 10000, x = function_user(0)],
        each not List.IsEmpty( [x][result] ),
        each [Offset = [Offset] + 10000, x = function_user([Offset]) ],
        each [x]
    )
in
    Source

 
Capital O in the 5th line for the first Offset --> each [Offset = [Offset]


This @rbreneman for this it works really well for my ServiceNow report.