The idea of creating a Power BI custom connector for the Power BI REST API is not a new one: Miguel Escobar wrote one earlier this year (see here for the source code and documentation). However the Power BI REST API has a lot of endpoints so writing code to support them all would be extremely time-consuming, and there is another option: using the new OpenApi.Document() M function – only available for custom connectors, and not in Power BI Desktop or Excel – to read the API definition from an Open API (aka Swagger) definition file.
Full documentation and samples for using OpenApi.Document are available here, and an Open API definition file for the Power BI REST API is available here. The Power BI REST API uses Azure Active Directory authentication and handling AAD authentication in custom connectors is well documented here; Miguel’s sample shows how to adapt this code for Power BI.
After that, just download the Power BI REST API definition, save it as a .json file and add it to your Visual Studio custom connector project and set the Build Action property of the file to Compile:
You can then use the Extension.Contents() M function to read the contents of the file in code and pass that to OpenApi.Document(). The last problem to solve is that OpenApi.Document() only supports basic and anonymous authentication by default, so support for OAuth2 has to be handled using the ManualCredentials option. Here’s the M function definition from my demo project showing how to do this:
[sourcecode language=”text” padlinenumbers=”true”]
[DataSource.Kind="OAuth2Demo", Publish="OAuth2Demo.Publish"]
shared OAuth2.OpenAPIDemo = () =>
let
OAuthCredential = Extension.CurrentCredential(),
OAuthToken = OAuthCredential[access_token],
SecurityHeaders = [ Authorization = "Bearer " & OAuthToken],
PBISwagger = Extension.Contents("PBISwagger.json"),
CallWebService =
OpenApi.Document(
PBISwagger,
[ Headers = SecurityHeaders, ManualCredentials = true ]
)
in
CallWebService;
[/sourcecode]
Once you build your custom connector, you see all of the endpoints (or at least those that support GET and POST requests) exposed as functions in the Navigation table:
It looks super-easy to build a custom connector this way, but is it a actually good idea? I can see a few pros and cons:
- As you’ll find if you try this, not everything ‘just works’ – there are a few functions that return errors.
- In this case I embedded the Open API definition file in the project but it’s also possible to download it dynamically using Web.Contents(). This means that whenever the published Open API definition file is updated the custom connector also updates automatically. Are you sure the published Open API definition file will get updated when the API changes, though? What if it doesn’t, or there is an error in it?
- Connecting to an API is all very well but it’s very important that a custom connector delivers data in a format that is modelled appropriately for Power BI, and very few APIs do this on their own. Pretty much every custom connector I have built has a lot of code in it to transform the data output by the API into something like a star schema, and the need to do this cancels out all the magic automatic stuff that OpenApi.Document() does for you.
This is great stuff and works like a charm all the way up until trying to add this to gateways. I’m unable to provide the OAuth credentials necessary to create the connection. Is it possible to use this custom connector in gateways or not?
Great question, I’d really like to know if the gateway will support the automatic refresh for the custom connector. Does it need to be certified in order to do that?
I would also like to know this
Gateways do support custom connectors for all types of refresh, including scheduled refresh. The custom connectors do not need to be certified.
Thank you for your content, I’m trying to do a connector that will load dynamic parameters and, for this, I’m using OpenAPI Spec, I’d like to know how to treat (If it is possible) data when we use this approach in example
let
url = “https://api.domain.com”,
content = Web.Contents(url),
items = OpenApi.Document(content, DefaultOptions),
#”CreateTable”. = Table.FromList(.
This was really helpful. I have made a successful oauch2 flow to https://api.eloverblik.dk/thirdpartyapi/index.html (Danish national energy datahub)