Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Afternoon All!
Gonna be honest i'm racking my **bleep** brains out with this one but i'm getting no where.
I tested a rest function in Postman and all works brilliant returning the data i need. unfortunately i want to build this into a power query now.
to generate a Oauth2 key i need to send a refresh key with username and password in order to get a return , then use the vaule it returns to request a report.
I'm hoping some wise individual will have at least some idea how to pull this off as i cannot i've tried using the web API fuction built into PQ but its just doesn't allow me to do anything. any ideas?
Thanks in advance 🙂
Solved! Go to Solution.
Great! Cheers!
The response is in Json format, so you just need to add a step at the end of the custom function to extract access_token value. Don't forget to add a comma at the end of previous response step.
.....................
response = Json.Document(Web.Contents(.......................)),
access_token = response[access_token]
in
access_token
Jing
I am trying to use everybody's code snippets to connect to Channel Advisor's API. This part works:
() =>
let
url = "https://api.channeladvisor.com/oauth2/token",
headers = [#"Authorization"="Basic xxxxxxx="],
postBody = [
grant_type = "refresh_token",
refresh_token = "xxxxxxxx"
],
response = Json.Document(Web.Contents(url,
[
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(postBody))
])),
access_token = response[access_token]
in
access_token
What doesn't work is this part:
let
Source = Json.Document(Web.Contents("https://api.channeladvisor.com/oauth2/token",
[Headers=[#"Authorization"="bearer " & GetAccessToken(),
#"accept" = "text/plain",
#"Content-Type"="application/json"],
ManualStatusHandling = {404, 400}]))
in
Source
I have tried it with "Bearer" vs. "bearer" and a few other things, but either I get a 400 error OR the Power Query output simply gives me a small table that says "error | invalid client".
What am I doing wrong?
hi @Anonymous ,
Could you try to replace your last query with:
let
Source = Json.Document(Web.Contents("search_url",
[Headers=[#"Authorization"= GetAccessToken(),
#"accept" = "text/plain",
#"Content-Type"="application/json"],
ManualStatusHandling = {404, 400}]))
in
Source
Thank you for responding. If I make that change, I get this error:
DataSource.Error: The remote name could not be resolved: 'search_url'
Details:
search_url
Replace search_url in the query with your url: https://api.channeladvisor.com/oauth2/token
That produces the "error | invalid client" response.
I really only want order information at this point. When I tried it with https://api.channeladvisor.com/v1/orders as the URL, I got a different error:
Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer
What is in the documentation from the API? Did you try to connect with for example Postman (https://www.postman.com/)? Did you get a succesvol connection?
If your not familiar with Postman:
Postman is an API platform for building and using APIs. Postman simplifies each step of the API lifecycle and streamlines collaboration so you can create better APIs—faster.
I'll try that. Thanks!
@SomeDataDude I set up Postman and was able to connect as follows:
GET https://api.channeladvisor.com/v1/orders
Authorization: Bearer token (using the initial access token from Channel Advisor)
Body: none
When I try to enter a refresh token, the whole thing errors out.
How do I now take what is working in Postman and alter the initial code for the function and query? What I have tried is to enter that information into my initial code, but it doesn't work. It comes back and says something about connecting anonymously.
OK, so for future reference, in case there is somebody else searching for the complete answer, here is what I did after @SomeDataDude put me on the right track. My goal was to connect to Channel Advisor orders.
() =>
let
url = "https://api.channeladvisor.com/oauth2/token",
headers = [#"Authorization"="Basic XXXXX"],
postBody = [
grant_type = "refresh_token",
refresh_token = "XXXXX"
],
response = Json.Document(Web.Contents(url,
[
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(postBody))
])),
access_token = response[access_token]
in
access_token
This gets the current access token (= bearer token in Channel Advisor "speak") based on the refresh token.
This is the initial query:
let
Source = Json.Document(Web.Contents("https://api.channeladvisor.com/v1/orders",
[Headers=[#"Authorization"="bearer " & GetAccessToken(),
#"accept" = "text/plain",
#"Content-Type"="application/json"],
ManualStatusHandling = {404, 400}]))
in
Source
And this is the output from that query:
Click on the word "List" in the table, next to the word "value". This inserts a step "Navigation" into the query. It also opens up the list an a tab called "List Tools" at the top. Click on "Convert | To Table" on the ribbon in the "List Tools" tab:
It will present you with a dialog box. I simply clicked "OK":
The last step is to expand the resulting column:
Uncheck "Use original column name as prefix" and click "OK". Voila, here is your data.
Make sure that both data sources are set to connect anonymously. Otherwise, you will get an error. Also, try things in Postman first if you run into trouble.
Hi @TrysHisBest
Do you send a Post request to get the key? If so, you need to put request body in Content parameter of Web.Contents function.
Here is an example for Post request. Create a blank query, open its Advanced Editor, remove the code there and paste below code to it.
let
url = "https://xxxxxxxxxxxxxxxxxxxxxxxx",
headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
postBody = [
grant_type = "xxxxxxxxxx",
username = "xxxxxxxxxxxxxxxx",
password = "xxxxxxxxxxx"
],
response = Json.Document(Web.Contents(url,
[
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(postBody))
]))
in
response
Once you get the key, you can then use it in another Web.Contents statement to get data for your report.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi Jing,
I've tried this and had this come back as the first response?
DataSource.Error: Web.Contents failed to get contents from 'https://api.channeladvisor.com/oauth2/token' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.channeladvisor.com/oauth2/token
Url=https://api.channeladvisor.com/oauth2/token
After that i tried ammending to add in the refresh token but met with the same response above?
let
url = "https://api.channeladvisor.com/oauth2/token",
headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
postBody = [
grant_type = refresh_token,
refresh_token ="xxxxx"
username = "xxxxxxxxxxxxxxxx",
password = "xxxxxxxxxxx"
],
response = Json.Document(Web.Contents(url,
[
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(postBody))
]))
in
response
Hi @TrysHisBest
You need to modify the headers and body content according to your POST request. Can you provide a screenshot of the correct request in Postman (remove sensitive info)?
I think grant_type value should also be wrapped by double quotes.
grant_type = "refresh_token",
Jing
Hi @v-jingzhang ,
Really do apprecaite the help.
This is what i made in postman and getting the auth key from. works no problem
Then have a seperate tab to use a GET request to get the data i need from a different url with the Auth02 key.
Again thanks very much 🙂
Hi @TrysHisBest
Your username and password are passed in Authorization Header with Basic Auth type, not in Body, so they should be in Headers parameter rather than Content parameter of Web.Contents().
I did some google searching for passing basic Authorization username and password, and found a blog and a thread:
Using Power Query to get data from an API that uses a Username and Password combination
Passing username and password in Basic authentication (microsoft.com)
Both suggest to put username and password in username:pasword format, then encode the string with base64 encoding. Then put the encoded string into Headers. For encoding use the link https://www.base64encode.org/
Based on above, you could try below code
let
url = "https://api.channeladvisor.com/oauth2/token",
headers = [#"Authorization"="Basic xxxxxxxxxxxxx"],
postBody = [
grant_type = "refresh_token",
refresh_token = "xxxxxxxxxxxxxxxx"
],
response = Json.Document(Web.Contents(url,
[
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(postBody))
]))
in
response
If you can get correct token by using above code, then you can transform the query to a custom function so that it can be called by other queries. Just add () => before let:
() =>
let
url = "https://api.channeladvisor.com/oauth2/token",
headers = [#"Authorization"="Basic xxxxxxxxxxxxx"],
postBody = [
grant_type = "refresh_token",
refresh_token = "xxxxxxxxxxxxxxxx"
],
response = Json.Document(Web.Contents(url,
[
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(postBody))
]))
in
response
Then create another query for the GET request which calls the function. Assume the earlier custom function query is called GetAccessToken.
let
Source = Json.Document(Web.Contents("https://xxxxxxxxxxxxxxxxxxxx", [Headers=[#"Authorization"="Bearer "&GetAccessToken()]]))
in
Source
Hope this helps.
Jing
Hi Jing,
Thank you for providing the solution. After trying your code, I got following error "DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
DataSourceKind=Web
DataSourcePath=https://api-eu.ariba.com/v2/oauth/token"
Could you please help us out here?
Hi @v-jingzhang,
Thanks for sharing the solution. Could you help me with the following problem?
I created the function, that is working fine. But if I put the function in the second query, it doesn’t work. When I put the token directly in the query it works fine. I use the following M code:
let
Source = Json.Document(Web.Contents("URL",
[Headers=[#"Authorization"="Bearer "&GetAccessToken(),
#"accept" = "text/plain",
#"Content-Type"="application/json"],
ManualStatusHandling = {404, 400}]))
in
Source
My custom query is called GetAccessToken.
What am I doing wrong?
This is the error I get: Formula.Firewall: Query 'Query5' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.
As it says. Modify the query privacy settings so that they match. Or Inline the token query.
Hi @lbendlin ,
Thanks for your reply. Which options do I have to modify te query privacy settings? Inline the token is not an option, because it is a refresh token. I don’t want to replace the token every time I refresh the data.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
62 | |
40 | |
36 | |
28 | |
15 |