Power BI Insights: Connecting with REST APIs; Adding report names

September 12 2021

Power BI pros discuss connecting with REST APIs using OAuth2 authentication and adding report names to a Log Analytics report.

Connecting with REST APIs using OAuth2 authentication for Power Query and Power BI

Chris Webb explained that there are many articles explaining how to carry out OAuth2 authentication when connecting to REST APIs from Power BI and Power Query. But for users wanting to connect with a REST API from Power BI, the best answer is a custom connector.

Webb linked to documentation on the process, but cautioned that users can only connect to some APIs with AAD authentication using OData connectors or pre-built web connectors. Currently, Excel Power Query does not allow for custom connectors and Power BI Service custom connectors need to be accompanied with an on-prem gateway.

A quick web search will turn up several examples of how to implement an OAuth2 credential flow in regular Power Query queries without needing a custom connector. This is not recommended: it’s not secure and it’s not reliable. In particular, hard-coding usernames/passwords or client ids/client secrets in your M code is a really bad idea. What’s more requesting a new token every time a query runs isn’t great either.

Adding report names to a Log Analytics report

About MSDW Reporter

More about MSDW Reporter