The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I need to query data from a REST API but couldn't find any appropriate connector, or even a generic connector. Such a connector should be able to handle requests in any HTTP method, manage authentication by passing a user name and password as parameters of a POST request and using the returned token in the header of subsequent requests, handle pagination of results (i.e. looping queries until there's no next page)...
A generic connector may not be possible, and I may need to create a custom connector, but it's a steep learning curve, so I was hoping for at least something that I can start from and modify.
Any hint?
Solved! Go to Solution.
Hi @mrgou,
You can try to use the following M query code if they work on your side:
let
rooturl = "https://myserver.com/",
//profile id
oath_oidc_profile_id = "xxxxxx",
//access token from your portal configurations
token = "xxxxxxx",
relativeURL =
"auth/oauth/session/"
& oath_oidc_profile_id,
//connect to server
GetJson =
Web.Contents(
rooturl,
[
Headers = [
#"Authorization" = "Bearer" & token,
#"Content-Type" = "application/x-www-form-urlencoded"
],
RelativePath = relativeURL
]
),
//'session Id' for advanced operation
sessionId = Json.Document(GetJson)[sessionId],
Source =
Web.Contents(
rooturl,
[
Headers = [
#"Authorization" = "Bearer" & token,
#"Content-Type" = "application/x-www-form-urlencoded"
],
RelativePath = "xxxx/xxxxx/xxxxx",
Content = Text.ToBinary("client_id=" & sessionId)
]
)
in
Source
If the above not help, you can also take a look at the following link about pull data from the rest API with authentication:
Pull data from a REST API Authentication
Regards,
Xiaoxin Sheng
This is not "generic" as it is specific for Zendesk. But it should be possible to make it generic https://community.fabric.microsoft.com/t5/Desktop/Custom-Zendesk-Connector-Power-Query-Code/td-p/240....
The code to generate the token seems to work great, except when I use it against an api endpoint where there are so many records that the token eventually expires. It seems that the code only uses the same token over and over.
See my issue here:
Hi @mrgou,
You can try to use the following M query code if they work on your side:
let
rooturl = "https://myserver.com/",
//profile id
oath_oidc_profile_id = "xxxxxx",
//access token from your portal configurations
token = "xxxxxxx",
relativeURL =
"auth/oauth/session/"
& oath_oidc_profile_id,
//connect to server
GetJson =
Web.Contents(
rooturl,
[
Headers = [
#"Authorization" = "Bearer" & token,
#"Content-Type" = "application/x-www-form-urlencoded"
],
RelativePath = relativeURL
]
),
//'session Id' for advanced operation
sessionId = Json.Document(GetJson)[sessionId],
Source =
Web.Contents(
rooturl,
[
Headers = [
#"Authorization" = "Bearer" & token,
#"Content-Type" = "application/x-www-form-urlencoded"
],
RelativePath = "xxxx/xxxxx/xxxxx",
Content = Text.ToBinary("client_id=" & sessionId)
]
)
in
Source
If the above not help, you can also take a look at the following link about pull data from the rest API with authentication:
Pull data from a REST API Authentication
Regards,
Xiaoxin Sheng
Hi,
I have two questions,
First, It is my understanding this would only work locally and not in the service. As it will treat "Web.Contents(<url variable>" in this case "Web.Contents(rooturl" as a dynamic connector and thus will not refresh. Changing it to Web.Contents("https://myserver.com/" .. would result in the service removing this error. The service might still show the "invalid credentials error". According to Chris Webb this can be fixed in code, but i am not sure how. The easier option is just to turn off the credential check in the service. It does need more testing but it seems to work.
Last question, is around this line here:
sessionId = Json.Document(GetJson)[sessionId]
I had a similar code:
Json = GetJson(Url), //Getjson return a Json.Document.
Value = Json[#"tickets"]
Which I tried to parameterize.
//this works
Value = Json[tickets]
//but this does not:
var = "tickets"
Value = Json[var]
//ended up with:
var = "tickets"
Value = Record.FieldValues(Record.SelectFields(Json, var)){0}
There has got to be single or at least simplier method... right?
@v-shex-msft Thank you for sharing this. I looked into the M reference, and also found a WebAction.Request method that appears to allow using other HTTP methods. I guess I'll have to go ahead and study the language. Unfortunately, I couldn't find any good learning resource, so I'll have to do it the hard way with the specification and reference documentation from Microsoft!
HI @mrgou,
If you can't find enough code sample/tutorials of power query functions. You can also take a look at Chris Webb's blog, he shares lots of blogs of power query function usage:
Chris Webb's BI Blog # Web.Contents
Regards,
Xiaoxin Sheng
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
14 | |
12 | |
3 | |
2 | |
2 |