Since the inception of Power BI Custom Connectors, I’ve been drawn to coming up with new ones that could help my life and the lives of my customers. One of the most helpful custom connectors that I’ve created so far is the Power BI REST API Custom Connector (url).
However, in order for people to use it they had to go through a really complex set of steps where they had to register their own application, modify the custom connector until they were able to actually launch the connector in their Power BI Desktop.
A big chance occured not so long ago – a new Authentication kind has been added to the Power Query SDK called the AAD (Azure Active Directory) kind which is specific for resource that use the AAD. This is similar to how you might use the ADAL library when developing a solution in .NET. I’m no programmer / developr, so I have little to no experience working with ADAL or MSAL, but this implementation of the authentication kind goes beyond just a simple library, as it also provides an embedded first-party application created by Microsoft for anyone to use.
What does this mean? It means that it’s much easier to use the connector now as it doesn’t require anything else other than the connector file itself.
In short, with this new feature for the custom connector, all you need to do to connect to the Power BI REST API from within Power BI is to:
- Download the .mez file from the GitHub repo
- Save the file in your custom connectors folder ([Documents]\Power BI Desktop\Custom Connectors). If the folder doesn’t exist, create it and plac the file in there.
- In Power BI Desktop, select File > Options and settings > Options > Security. Under Data Extensions, select (Not Recommended) Allow any extension to load without validation or warning. Select OK, and then restart Power BI Desktop.
- Open Power BI Desktop and find the connector listed inside the Get Data window
Features found in the connector
With this connector you are able to connect to almost every single GET endpoint from the REST API. Most importantly, if you don’t find it in the long list of tables available, there’s a GETData function so you can insert any url of the Power BI REST API and query the data that you need.
One of the coolest features that I was able to introduce was a way to get the Event Activity Log data inside of Power BI through a function. Not only that, but the function itself can be used for Incremental Refresh, meaning that we could absolutely compile all of the event activity log data into a single dataset and analyze everything and anything with ease. You can read more about how to use that function from this article (url) that I wrote a few years ago.
How to schedule a refresh in the Power BI Service
Since this is a Power BI Custom Connector, it follows the same set of rules as other custom connectors. You can read Microsoft’s official documentation for the step by step on how to configure the scheduled refresh, using a gateway, of your dataset sourced from a data connector from here (url).
I’ve tested this connector for the past few years in small (dozens of workspaces and users) to big environmnts (hundreds of workspaces and users), but I’ve never attempted to use this custom connector in a huge environment with thousands of workspaces and users under the same tenant. In those cases perhaps a more granular and programmatic approach might be necessary.
The primary reason for this is that the REST API is not necessarily fast in sending the data, but the connector has been purposefully built in a way that query folding is not enabled and there’s no schema defined for most tables in an effort to make this a connector that will simply always show you all the data that we get directly from the REST API in the easist to manage way. I’ve chosen usability over performance. In theory, this will impact tenants which have ‘larger than life’ workspaces and users.
If you’re getting serious about managing your Power BI tenant, then this is a must have tool for you. Give it a try and if you find any issues please report them on the GitHub repo!
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.