Need help Refreshing a dynamic dataset with cursor pagination

[apvu2]

So I am using cursor paination and I am not sure if I can use RelativePath as my next changes my M code is below. I dot know how to go about it and considered using perhaps python that has the data in a csv and seting up a refresh using windows task. That would be my last resort though..



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),
linksRecord = initialData[links],
nextUrl = linksRecord[next],

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

// Loop for pagination
loopCondition = nextUrl <> null,
paginatedData = List.Generate(
() => [url = baseUrl, nextUrl = nextUrl],
each [nextUrl] <> null,
each [url = [nextUrl], nextUrlRecord = (fetchData([nextUrl]))[links], nextUrl = nextUrlRecord[next]],
each fetchData([url])
),
// 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", {"ticket_metrics", "meta", "links"}, {"ticket_metrics", "meta", "links"}),
#"Expanded ticket_metrics" = Table.ExpandListColumn(#"Expanded Column1", "ticket_metrics"),
#"Expanded ticket_metrics1" = Table.ExpandRecordColumn(#"Expanded ticket_metrics", "ticket_metrics", {"url", "id", "ticket_id", "created_at", "updated_at", "group_stations", "assignee_stations", "reopens", "replies", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at", "reply_time_in_minutes", "first_resolution_time_in_minutes", "full_resolution_time_in_minutes", "agent_wait_time_in_minutes", "requester_wait_time_in_minutes", "on_hold_time_in_minutes"}, {"url", "id", "ticket_id", "created_at", "updated_at", "group_stations", "assignee_stations", "reopens", "replies", "assignee_updated_at", "requester_updated_at", "status_updated_at", "initially_assigned_at", "assigned_at", "solved_at", "latest_comment_added_at", "reply_time_in_minutes", "first_resolution_time_in_minutes", "full_resolution_time_in_minutes", "agent_wait_time_in_minutes", "requester_wait_time_in_minutes", "on_hold_time_in_minutes"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded ticket_metrics1",{"ticket_id", "solved_at"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"solved_at", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([solved_at] <> null) and ([ticket_id] = 100075))
in
#"Filtered Rows"

[Atif2917]

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.