Forum Discussion
Paginated POST Request Fails with Anonymous Permission Error After Several Pages?
I've been working on this for a few hours now so forgive my callousness on this one, but I've tried every solution I could find from online and can't figure this one out. The code provided below is for a specific API that you won't have access to, so it's not a reproducible example, but I know this issue extends to other use cases.
In Desktop > Power Query/M, I am attempting to query a POST endpoint ("/api3/Customer/GetCustomers", yes, it IS a POST endpoint) that has pagination. The below code works, ONLY IF I employ the "TEST" line, where I only query for the first few pages, however if I do the complete while loop attempting to gather ALL of the pages, eventually it fails with a Permissions error:
let
// API Credentials
clent_id = "the_clent_id",
clent_secret = "the_clent_secret",
username = "the_username",
password = "the_password",
// Base URL
base_url = "https://portal.the_api_url.com",
// Function to send POST request for the current page number
FetchPage = (pageNumber) =>
let
// Get Auth Token first
header = [#"Content-Type"="application/x-www-form-urlencoded"],
body = "client_id="& clent_id & "&client_secret=" & clent_secret & "&username=" & username & "&password="& password & "&grant_type=password&scope=account",
source = Json.Document(Web.Contents(base_url, [RelativePath="/api3/token", Headers=header, Content=Text.ToBinary(body)])),
authToken = source[access_token],
data = Json.FromValue([DealerId = "the_dealer_id",
DealerCode = "the_dealer_code",
Code = "All",
PageNumber = pageNumber,
PageRows = 50,
]),
headers = [#"Content-Type" = "application/json", #"Authorization" = "Bearer " & authToken],
APiData = Web.Contents(base_url, [RelativePath="/api3/Customer/GetCustomers", Content=data, Headers=headers]),
responseText = Text.FromBinary(APiData),
parsedResponse = Json.Document(responseText),
Result = parsedResponse[Result]
in
Result,
// // Function to query for all pages
// listOfPages = List.Generate(
// () => 1, // Start with page 1
// each not List.IsEmpty(FetchPage(_)), // Continue until FetchPage returns empty list
// each _ + 1, // Increment page number
// each FetchPage(_) // Fetch data for current page
// ),
// TEST
// Generate list of pages from 1 to 5
listOfPages = List.Transform({1..5}, each FetchPage(_)),
// Convert master list of records into a table
TableResult = Table.FromRecords(List.Combine(listOfPages))
in
TableResult
Using the while loop is clearly working as it runs for several minutes, and shows about 10,000 rows being gathered in the Load screen when it's running, but again after a while, the "Edit Permissions" menu shows up asking me to select "Anonymous" for the data source setting, which it already is. I've looked in both the Local and Global data source settings, both are already set to Anonymous. Clicking Connect here just presents the same screen again and again. I've tried the different values in the level drop down, same issue. If I run the script with a finite number of pages, it'll work without issue, but does this after about 10 minutes of running.
Why might this be happening? I initially thought maybe the API was kicking me out after a while, so as a test for that I made the exact same code in a Python script, and it ran successfully from start to finish, so it's definitely an issue with Power BI and its permission handling. Not sure what else I can try here, any suggestions?
2 Replies
- lbendlin
Super User
Your token is expiring.
- windowshoprFrequent Visitor
How so? I'm requesting a new auth token with every call as part of the page request method.
EDIT
also as I mentioned, I made the exact same script as a Python equivalent and it runs fine, so no I don't believe the token expiring is the issue here.