API call works in Desktop, but in Service it fails

[phk_holship]

I have an API call that is working fine in desktop, but as soon as I try to upload it, it will fail - giving data source errors "bad request" or "bad syntax". Yet the syntax and request is identical to desktop of course. I have tried Grok, Gemini, ChatGPT -. they all just go in circles. I am not sure what I can do, if anything, to fix this issue.

The API works with a "CURL" request, which in power query is converted to a "Web.Contents()" request but that doesn't seem to work in Service.

curl --request GET \
--url https://openapi.pleo.io/v1/expenses \
--header 'accept: application/json' \
--header 'authorization: Bearer APITOKENHERE'

[v-kathullac]

Hi @phk_holship ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @phk_holship ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @phk_holship ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[phk_holship]

There was no solution? Just a workaround i already found. Still would like to have a real solution.

[v-kathullac · Accepted solution]

Hi @phk_holship ,

Am attaching a similar type of thread link there they also used some API's to refresh there dataset see the thread and follow the steps or the lines of code what they used and what not done by you i think this will get your issue resolved.

Solved: Refreshing data from REST API works on Desktop, fa... - Microsoft Fabric Community

Regards,

Chaithanya.

[lbendlin]

This should work. Please show a sanitized version of your Power Query code

Please follow the documentation. Use Accept, RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[phk_holship]

Yep here is the code:

let
response = Web.Contents(
"https://openapi.pleo.io/v1/expenses",
[
Headers = [
#"accept" = "application/json",
#"authorization" = "Bearer APITOKEN"
]
]
),
json = Json.Document(response),
expenses = json[expenses],
expenses1 = expenses{0}
in
expenses1

[lbendlin]

what is the raw return value of "response" ?

Try this version

let
response = Web.Contents(
"https://openapi.pleo.io/v1/expenses",
[
Headers = [
Accept = "application/json",
Authorization = "Bearer APITOKEN"
]
]
),
json = Json.Document(response),
expenses = json[expenses],
expenses1 = expenses{0}
in
expenses1

[phk_holship]

Same problem unfortunately. The raw value is a list of expenses, I just attach the  top part of it here:

[lbendlin]

that's your Postman response. What's the response from Power Query?

[phk_holship]

How do I check that?

[lbendlin]

By clicking on the applied step entry.

[phk_holship]

There are several, attached here:

[lbendlin]

Looks great. Convert to table, expand records to columns and implement the pagination logic via the nextpageoffset.

[phk_holship]

Yeah already there, it works fine in excel and in powerbi desktop. But as soon as I upload to the service it fails.

I have built a work around now where I refresh an excel sheet daily via task scheduler, and then I pull that excel sheet into powerbi desktop, and from there into service, and then it works

[v-kathullac]

Hi @phk_holship ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Hera are the few points to solve your issue and mentioned few points that are impacting your development

• Power BI Desktop supports full API access, including custom headers and dynamic URLs, but Power BI Service restricts these features for security, often causing errors like “Bad Request” or “Bad Syntax.”

• To avoid blocking in the Service, use Web.Contents() with structured arguments such as Relativepath and Headers instead of dynamic URLs or query strings.

• Bearer tokens and custom headers typically fail in Power BI Service unless used through custom connectors or parameterized queries with an appropriate gateway setup.

• Power BI Service requires a data gateway to access on-premise sources or unsupported APIs; ensure the API domain is registered as a data source in the gateway settings.

• A reliable workaround is to fetch API data externally (e.g., via Python, PowerShell, or Task Scheduler), save it to Excel or CSV, and connect Power BI to that file via OneDrive/SharePoint for scheduled refreshes.

• Microsoft recommends alternatives like Azure Functions, Logic Apps, or Power Automate to call APIs securely, store results in supported storage, and keep your Power BI reports up to date.

Regards,

Chaithanya.