Web Source to Avoid Power BI Gateway with Azure Functions

[ibarrau]

First, a bit of theory. There are web scenarios that require a gateway for processing. Even when using Power Query online (dataflows), we will need one. There are three classic functions of interest that behave differently: WebContent, WebPages, and WebContentBrowser. If you want to understand the differences and which requires a gateway, you can read the following documentation: https://learn.microsoft.com/en-us/power-query/connectors/web/web-troubleshoot

I would like to clarify that I will not be providing an introduction to Azure Functions. I won't explain what it is, how it works, or how to set up the environment. There are already excellent videos online, or you can read more in the following Microsoft documentation: https://learn.microsoft.com/es-es/azure/azure-functions/create-first-function-vs-code-python?pivots=...

For this post, basic Python knowledge and familiarity with Visual Studio Code are required. Assuming you have the environment set up with the necessary extensions in Visual Studio Code, let’s start with a simple example of connecting to an API. Once logged into Azure and having visibility of our subscription, we will create a Function App. Think of it as the processing server for many Azure Functions. In this space, we can have multiple functions, considering each one as a request.

When creating it, there are four steps.

That name will be part of the URL of the API we are generating.

Now we can create the function. It will be stored in the folder pointed to by Visual Studio Code. Click on the lightning bolt to create a function within the Function App.

The created application will look like this:

What we need to know is that we have a main function where the main code will execute. Depending on whether we call the function with GET or POST, we can capture URL or body parameters. This can help increase security since, without the correct parameters or authentication, we cannot obtain a response. As an example, we will create a simple logic that makes the URL return results every time it is called without needing anything else, as I consider the data of the workspace names in my demo tenant to be non-sensitive.

Let’s see how easy it is to write code within the main function that returns a dictionary or JSON in the return under the desired status code. We can leverage messages to be clear about errors so we can receive them in Power BI.

Using SimplePBI to obtain groups, you can see that we simply generate a token, create a group object (workspaces), and call the workspaces that our Service Principal can view.

Then I added an additional note to the return for when we want to return not a literal "text" but a dictionary or JSON, which is the "mimetype."

NOTE: If you don’t know what SimplePBI is, you can check it out here.

IMPORTANT: I want to clarify that we have a secret exposed in this code; the best practice for an Azure function like this would be to use an Azure Key Vault for our passwords and secrets so that they are not exposed.

If we are going to use an imported library, we will need to look for the requirements.txt file in the resource panel and add it. I did this for SimplePBI.

If we need to use pandas to extract data from a structured source, we can use "DataFrame.to_dict(orient="records")" in the json.dumps of the return to convert our frame to JSON mimetype format.

Get Data
That’s a lot about Python and functions; let’s go to Power BI Desktop to connect. We will use the web connector to bring the information with anonymous credentials.

Depending on how we format our returned JSON in the API we created in Azure Functions, we may need to perform transformations in Power Query. In this case, the data returned by SimplePBI is very uniform, and the engine practically resolves it automatically.

Let’s see how it looks:

Now we have our data loaded into Power BI using Python without needing a personal gateway. We can publish our report to the Power BI service and configure the credentials as anonymous.

NOTE: Azure Functions have a usage limit (timeout) of 5 minutes. Our execution cannot exceed this time; otherwise, it will fail, and our purpose will be lost.

Conclusion

This methodology can help speed up small developments, scrapes, or complicated cloud sources that Power BI does not support without a driver connector (e.g., Oracle or MySQL). With an Azure Function, it’s quick and easy to build an API that responds. To enhance security, it’s essential to use Azure Key Vaults in our code, and if we need to expose more sensitive data, it’s best to request a parameter or body with some kind of key (which we can invent) so that not everything is left on a public URL. I hope this example sparks new ideas for you. You can find the code in my github.