Connect to API through Power Query doesn´t work, but does in Power Automate

[Hamp79]

Hi, would be possible to give me support with this issue i am facing.

Having a token I am able to connect to one of my company`s Api with power automate, using these fields:
Method: Get
Header ({"Authorization","Ocp-Apim-Subscription-Key"}
Authentication = None

But I try to replicate in Power query, but without success

ApiResponse = Web.Contents("xxxxx", [
Headers = [
// #"Ocp-Apim-Subscription-Key" = "xxxx",
Authorization = "Bearer "& AccessToken,
#"Content-Type"= "application/json"),
json = Json.Document(ApiResponse),
table = Table.FromRecords(json)

I really appreciate any idea how to solve this in Power Query

Thanks

[Sri1999]

I am able to get all of my org data with the given power query. Hope it might help you.

let // Define API details
tokenUrl = "https://",
apiUrl = "https://",
TokenBody = [
grant_type = "client_credentials",
client_Id = " ",
client_Secret = " "

],

// Get access token
tokenResponse = Json.Document(Web.Contents(tokenUrl,
[
Content = Text.ToBinary(Uri.BuildQueryString(TokenBody)),
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded"
],
ManualStatusHandling = {400, 401, 403}
]

)),

accessToken = if Record.HasFields(tokenResponse, "access_token") then tokenResponse[access_token] else error "Failed to get access token",

// Call API with the access token
apiResponse = Web.Contents(apiUrl,
[Headers = [
#"ST-App-Key" = " ",
Authorization = "Bearer "& accessToken]]),
json = Json.Document(apiResponse),

BusinesUnitList = if Record.HasFields(json, "data") then json[data] else error "No data field found",

// Convert to table
Table = Table.FromList(BusinesUnitList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table

[v-sgandrathi]

Hi @Hamp79,
Thanks for using Microsoft Fabric Community Forum.

Here are some suggested improvements that may help resolve your issue:
Try using scope = "api://xxxxx/.default" Instead of scope = "api://xxxxx". This is a common requirement for OAuth authentication to request the necessary permissions.

Ensure that the token is applied correctly by using "Bearer " & AccessToken. This dynamically retrieves and applies the token for authentication.

Use RelativePath = "/GetASSIGNED_TO".  This improves performance when working with APIs that have multiple endpoints.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thankyou.

[Hamp79]

Thanks @v-sgandrathi, for your response.
I guess that those changes may help, but regretfully to me doesn´t work yet.

[v-sgandrathi · Accepted solution]

Hi @Hamp79,

As trouble shooting steps doesn’t resolve your issue, we recommend raising a support ticket with Microsoft for further assistance.

To raise a support ticket for Fabric and Power BI, kindly follow the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Thank you for being a part of Microsoft Fabric Community Forum!

[Hamp79]

Hi again, here it is... Thanks in advance.

let
//
TokenUrl = "xxxxx/oauth2/v2.0/token",
TokenBody = [
grant_type = "client_credentials",
client_id = "xxxxx",
client_secret = "xxxxxx",
scope = "api://xxxxx"
],
//
TokenResponse = Json.Document(Web.Contents(TokenUrl, [
Content = Text.ToBinary(Uri.BuildQueryString(TokenBody)),
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
])),
//
AccessToken = TokenResponse[access_token],

//
ApiResponse = Web.Contents("https://xxxxx/GetASSIGNED_TO", [
Headers = [
#"Ocp-Apim-Subscription-Key" = "xxxxx",
Authorization = "Bearer xxxxxx"
]
]),
json = Json.Document(ApiResponse),
table = Table.FromRecords(json)
in
table

[lbendlin]

and the error message?

[Hamp79]

DataSource.Error: Unable to connect to the remote server"

[Sri1999]

Thank you so much. i was struggling for a week. Finally I found the exact code that is working for me

[Hamp79]

 Hi, thanks. It is true, i forgot to say it..

"DataSource.Error: Unable to connect to the remote server"

I don´t know if it is a crazy idea, but is possible who set Api only had in account to be used by power automate,... i don´t know..

Thanks

[lbendlin]

Show a sanitized version of the code you are trying to use, and show the error message.