Swagger API Bearer Token not working

[MarcusR1]

I'm struggling to get PowerBI to connect to a Swagger REST API. Postman connects absolutely no problem, just add the Bearer token into the Authorization tab and run the query. Power BI simply refuses to connect and gives a 500 error. I have tried putting the Bearer token in the Basic Auth Username box (seems like a terrible hack to me!) as suggested by the PowerBI Community but that doesn't work. I've tried adding [Headers= into the Query, but the keyword Authorization is not recognized. This really should be a very simple thing to achieve, can anyone help?

[MarcusR1]

thanks @v-jianboli-msft I follwoed the instructions:

1) unticked the option and restarted PowerBI

2) Get Data > OData and pasted url (the same as Postman, so I know it works)

3) Immediately got this error, it doesn't even give me an option to change Auth mode (like it does in Excel)

Your help is really appreciated.

[v-jianboli-msft]

Hi @MarcusR1 ,

Please try if this M code could work:

let
//Replace your real token here
token = "xxxxxxx",
//Get Data (Replace your api url here)
apiUrl = "xxxxxx",
data  = Json.Document(Web.Contents(apiUrl,[Headers=[#"Authorization"="Bearer "&token,#"Content-Type"="application/json"]]))
in
    data

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MarcusR1]

Hi @v-jianboli-msft thanks for your suggestion, but this is what I tried in the first part, Authorization isn't allowed.

[v-jianboli-msft]

Hi @MarcusR1 ,

It sounds like you're having trouble connecting Power BI to a Swagger REST API.  You could try is to use the Web Connector in Power BI.

The Web Connector can handle JSON or XML responses from the API and parse them into tables that you can load into Power BI. You can also use Power Query to transform and filter the data as you wish.

For more details on how to use the Web Connector and Power Query with REST APIs, you can refer to this article, which explains the anatomy of REST APIs and how to plug them into Power BI.

Besides, here is a ducument about Troubleshooting the Power Query Web connector

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.