I'm using MS365 Business Premium Excel Power Query on PC
REF: Phil's great post ( https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query)
My objective is to get 48 months of historical data and then update my transaction table on a daily basis going forward.
ON DAY #1
I followed instructions and populated my API Key and Client ID, Set the Authentication to ANONYMOUS and Privacy Level to Organization (per Phil's guidance).
Next, I began the process of changing the Start & End date values in the PQ editor to begin downloading the data [30 days at a time]. Everything was going great, and I got about 23 months of historical data, I closed Excel & then I went home.
ON DAY #2
I came back, opened Excel and the file - went to PQ Editor to change the Start End date value - and got the error
I then clicked GO TO ERROR button and was brought to Phil's PQ line labeled 'data'.
I then clicked the EDIT Settings button.
and got the JSON prompt above.
I was stuck and could NOT move forward with getting PayPal transaction data.
So tried something, I closed the JSON prompt and then within PQ Editor chose DATA SOURCEs button to affirm the Data Source settings were anonymous and Privacy set to ORG.
But still have the ERROR when I refresh query.
I've repeated this WHOLE PROCESS on (2) different Windows PC's and get the same issue - works for DAY #1 - ERROR's on DAY #2.
Can you go to Data Source settings, and check if Privacy level is properly set?
Catalin - per my image titled DATASOURCE_2 - I did affirm the setting is correct.
In Power Query, when making web requests to APIs, the behavior can indeed seem a bit different compared to other environments where you explicitly specify request types like GET, POST, PUT, etc. This is because Power Query abstracts some of the lower-level details of HTTP requests to make it easier for users to fetch data from APIs without needing to understand all the intricacies of HTTP.
Power Query simplifies the web request process by abstracting away the need to explicitly set the HTTP method (GET, POST, etc.). This abstraction is designed to make it easier for users to connect to web services without needing deep knowledge of HTTP protocols. The method is implicitly determined based on the presence or absence of content:
- GET Request: If there is no content, Power Query assumes a GET request.
- POST Request: If there is content, Power Query assumes a POST request.
This design decision prioritizes ease of use over fine-grained control. However, it does mean that users have less flexibility compared to other environments where they can explicitly set the HTTP method.
I mentioned the above explanations only to underline that Power Query requests are a bit different than regular http requests.
The error you get might be due to Power Query caching or persisting certain settings between sessions, which could conflict with how it handles authentication and headers.
Potential Causes:
- Credential Caching: Power Query might cache credentials or session information between uses, and this can sometimes cause issues if the session is not properly re-authenticated.
- Data Source Settings: Even though you've set the data source to anonymous, the presence of an Authorization header might conflict with this setting on subsequent sessions.
- Session Expiry: APIs often issue tokens or sessions that expire after a certain period. If Power Query is relying on an expired token or session, it might fail without re-authentication.
What I would do:
1. Refresh the Preview : From PQ Editor>Home tab, click the Refresh Preview button. I found that sometimes, the error disappears if you refresh the data preview, it will clear the cached data preview.
If this does not work, try the next solution:
2. Clear Credential Cache:
Go to Data > Get Data > Data Source Settings.
In the window that appears, select the data source related to your API.
Click Clear Permissions to remove any cached credentials.
Re-authenticate when prompted on your next data load.
I had also bad experiences when trying to send POST requests using PQ Web.Contents function with Content attribute, only to find that it is unreliable, only a few requests actually reached the destination, some was simply ignored.
I prefer VBA for such requests, with ID and secret not set in code but in environment variables for safety.
