Need help Refreshing a dynamic dataset with cursor pagination

It seems like you're using Power Query M language to fetch paginated data from a REST API endpoint and load it into Power Query. You've already set up pagination using the nextUrl field to load additional pages of data.

If you want to use the RelativePath option to specify the next page URL, you can do that by constructing the full URL for the next page using the base URL and the next URL from the response. Here's how you can modify your code to achieve this:

m
Copy code
let
baseUrl = "https://Example.zendesk.com/api/v2/ticket_metrics.json?page[size]=100",

// Define a function to fetch data from a given URL
fetchData = (url) =>
let
response = Web.Contents(url),
json = Json.Document(response)
in
json,

// Initial call to fetchData
initialData = fetchData(baseUrl),

// Function to get the next URL from a JSON record
getNextUrl = (jsonRecord) =>
let
links = jsonRecord[links],
next = links[next]
in
next,

// List to store all paginated data
allData = {initialData},

// Loop for pagination
loopCondition = getNextUrl(initialData) <> null,
paginatedData = List.Generate(
() => [url = baseUrl],
each loopCondition,
each [url = baseUrl & "?page[size]=100&" & Text.From(getNextUrl(fetchData([url])))],
each let jsonData = fetchData([url]) in [url = Text.From(getNextUrl(jsonData)), data = jsonData]
),

// Convert the paginated data into a table
#"Converted to Table" = Table.FromList(paginatedData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data"}),
#"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {"ticket_metrics"}),
// ... continue with your data transformation steps
in
#"Filtered Rows"
In this modified code, we have added a getNextUrl function that extracts the next URL from the JSON record. Then, in the List.Generate function, we construct the full URL for the next page by appending the next URL to the base URL. This allows you to use the RelativePath option to specify the next page URL dynamically.

Please make sure to adjust the code to your specific needs, and test it with your API to ensure that it works correctly for your use case.