July 30, 2020
Hello,
I am having trouble looping through a Dataverse API. I am limited to 5000 records per page but need ~400,000 records. I am working on the code below to bring in multiple pages but am getting the following errors:
- Expression.Error: The name 'LoopThroughPages' wasn't recognized. Make sure it's spelled correctly.
- Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]
Please see my code below. Thank you in advance.
let
// Function to retrieve data
GetData = (url as text) as record =>
let
rawData = Json.Document(Web.Contents(url)),
data = rawData[value], // Adjust based on your API structure
nextLink = Record.FieldOrDefault(rawData, "@odata.nextLink", null),
result = [Data = data, NextLink = nextLink]
in
result,
// Function to loop through paginated data
LoopThroughPages = (url as text) as table =>
let
// Get data for the current page
pageData = GetData(url),
// Check if there is a next link
hasNextLink = pageData[NextLink] <> null,
// Recursive call to fetch more data if there is a next link
nextData = if hasNextLink then LoopThroughPages(pageData[NextLink]) else null,
// Combine data from the current page and the next pages
combinedData = if hasNextLink then Table.Combine({pageData[Data], nextData}) else pageData[Data]
in
combinedData,
// Execute the main query
result = LoopThroughPages("DataverseAPIforMyOrg")
in
result
October 5, 2010
Hi Matthew,
I don't get any errors with this code, I'd need your file to see how it is being executed against the data you are receiving.
Typically though if I was trying to get multiple pages of data from an API, I'd create a column in a table that lists the number of pages I want, then create a custom column that calls the API for each of those pages.
Something like this
PageNum |
1 |
2 |
... |
80 |
Then in your custom column call the API
LoopThroughPages([PageNum])
You'd need to modify your code accordingly to allow for passing the page num only.
Regards
Phil
1 Guest(s)