Forum Discussion
Yet another: "Can't refresh REST API with pagination query"
Hi Yxalitis
Sure, here you go. I've also formatted the code with Power Query Formatter.
let // Get parameters from the query editor BaseUrl = #"Base URL", // Referencing the Base URL parameter Token = #"Bearer Token", // Referencing the Bearer Token parameter PageSize = #"Page Size", // Referencing the Page Size parameter MaxPages = #"Max Pages", // Referencing the Max Pages parameter // Function to get data from ServiceNow with pagination GetData = (pageNum as number) => let // Build the URL dynamically for each page Offset = (pageNum - 1) * PageSize, // Use the Base URL for Web.Contents (hopefully resolves refresh issue) Url = BaseUrl, // Set up the query parameters in a record with one field per parameter Query = [sysparm_limit = Text.From(PageSize), sysparm_offset = Text.From(Offset)], // Set up the headers (Authorization using Bearer Token) Headers = [#"Authorization" = "Bearer " & Token], // Fetch the data from the API Response = Json.Document(Web.Contents(Url, [Headers = Headers, Query = Query])), // Access the 'result' from the response (adjust based on your API response structure) Result = Response[result], // Assuming 'result' holds the list of records // Convert the List into a Table ResultTable = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in ResultTable, // Retrieve data from multiple pages GetAllData = List.Transform({1 .. MaxPages}, each GetData(_)), // Combine the results into a single table CombinedData = Table.Combine(GetAllData) in CombinedDataHere is a rough summary of the code:
- Grabbing some parameters up front (BaseUrl to MaxPages).
- Defining a function GetData which
- Takes an argument page number (pageNum) and converts that into an Offset.
- It then queries the API for that page number using Web.Contents, with headers and query parameters passed in the 2nd argument (Response).
(Json.Document interprets the API response as JSON and converts it into a record format.) - Result takes the result field from the record Response.
- ResultTable converts to a table.
- GetAllData calls that function for the required number of pages
- CombineData Combines the results into a single table.
Hope this helps!
Glad to hear it š
Just FYI, if those additional filters are query parameters within the URL in this format:
?param1=value1¶m2=value2¶m3=value3...then you have the option of adding them to the Query record.
Based on the values I think I saw in your earlier post, it would be this kind of structure:
Query = [ sysparm_limit = Text.From(PageSize), sysparm_offset = Text.From(Offset), sysparm_query = "opened_atRELATIVEGT@month@ago@24", sysparm_display_value = "True", sysparm_fields = "number%2Csys_created_on%2Csys_created_by%2Csys_updated_on%2Cresolved_at" ],
Hi Yxalitis
The first thing that jumps out at me is that the query parameters should be moved to the options record, alongside Headers (similar to described here). This ensures that the first argument of Web.Contents doesn't change.
Here is how I would try writing the steps from Url to Response:
Url = BaseUrl,
Query =
[
sysparm_limit = Text.From(PageSize),
sysparm_offset = Text.From(Offset)
],
// Set up the headers (Authorization using Bearer Token)
Headers =
[#"Authorization" = "Bearer " & Token],
// Fetch the data from the API
Response =
Json.Document(
Web.Contents(
Url,
[Headers = Headers, Query = Query]
)
),
You could also look at providing RelativePath as another field in the record passed to Web.Contents (see the post linked above for an example), though this may not be required in this case.
Does refresh in the Power BI Service work using this type of setup?
Hi, thanks very much for your help!
OK, I updated my query to use your code, and it WORKS!!
Hi Yxalitis
Sure, here you go. I've also formatted the code with Power Query Formatter.
let // Get parameters from the query editor BaseUrl = #"Base URL", // Referencing the Base URL parameter Token = #"Bearer Token", // Referencing the Bearer Token parameter PageSize = #"Page Size", // Referencing the Page Size parameter MaxPages = #"Max Pages", // Referencing the Max Pages parameter // Function to get data from ServiceNow with pagination GetData = (pageNum as number) => let // Build the URL dynamically for each page Offset = (pageNum - 1) * PageSize, // Use the Base URL for Web.Contents (hopefully resolves refresh issue) Url = BaseUrl, // Set up the query parameters in a record with one field per parameter Query = [sysparm_limit = Text.From(PageSize), sysparm_offset = Text.From(Offset)], // Set up the headers (Authorization using Bearer Token) Headers = [#"Authorization" = "Bearer " & Token], // Fetch the data from the API Response = Json.Document(Web.Contents(Url, [Headers = Headers, Query = Query])), // Access the 'result' from the response (adjust based on your API response structure) Result = Response[result], // Assuming 'result' holds the list of records // Convert the List into a Table ResultTable = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in ResultTable, // Retrieve data from multiple pages GetAllData = List.Transform({1 .. MaxPages}, each GetData(_)), // Combine the results into a single table CombinedData = Table.Combine(GetAllData) in CombinedDataHere is a rough summary of the code:
- Grabbing some parameters up front (BaseUrl to MaxPages).
- Defining a function GetData which
- Takes an argument page number (pageNum) and converts that into an Offset.
- It then queries the API for that page number using Web.Contents, with headers and query parameters passed in the 2nd argument (Response).
(Json.Document interprets the API response as JSON and converts it into a record format.) - Result takes the result field from the record Response.
- ResultTable converts to a table.
- GetAllData calls that function for the required number of pages
- CombineData Combines the results into a single table.
Hope this helps!
This works amazing, but dumps the entire table.
I added the filters to teh end of the baseURL, and that worked!
Thanks so much!
Glad to hear it š
Just FYI, if those additional filters are query parameters within the URL in this format:
?param1=value1¶m2=value2¶m3=value3...then you have the option of adding them to the Query record.
Based on the values I think I saw in your earlier post, it would be this kind of structure:
Query = [ sysparm_limit = Text.From(PageSize), sysparm_offset = Text.From(Offset), sysparm_query = "opened_atRELATIVEGT@month@ago@24", sysparm_display_value = "True", sysparm_fields = "number%2Csys_created_on%2Csys_created_by%2Csys_updated_on%2Cresolved_at" ],