Forum Discussion
Looping M code function for ServiceNow API
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 SourceThis 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!
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.
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