Retrieving private API data using Python in Power BI / Power Query

Apr 06, 2023
#power-bi #power-query

Using an on-premises data gateway to query an API using Python for your dashboards.

Introduction

Power Query supports a myriad of data sources for your dashboards. Built-in connectors enable Power Query to query for data in Excel files, Sharepoint lists, Business Central, etc.

You can even retrieve data from a website (watch it parse the structured data of the CNN homepage!) or a public API using the Web connector. But if you’re planning to query an authenticated HTTP resource, you may not find any connector suitable for that specific authentication method.

In this post, I’ll describe the task of connecting Power BI to an IoT device’s web portal using a Salted Challenge Response Authentication Mechanism (SCRAM) as its login mechanism before offering its data.

What we need to know about this authentication method is that it involves multiple HTTP requests, encryption logic on the client, and an ability for the client to read response cookies / write request cookies.

Can the Web Power Query connector do all that? No.

Using M

Spoiler: this method won’t work

You can get far crafting the parsing script in Power Query’s M language. You’re able to make HTTP requests, parse responses, run SHA256 encryption, use for loops, … and having the entire script in Power Query means it’s kept as part of the Power BI report file without requiring any external dependency.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

let
    // Get login encryption params
    CapabilitiesUrl = "http://{host}/ISAPI/Security/sessionLogin/capabilities?username={username}",
    CapabilitiesResponse = Web.Contents(CapabilitiesUrl),
    
    SessionId = Text.BetweenDelimiters(CapabilitiesResponse, "<sessionID>", "</sessionID>"),
    Challenge = Text.BetweenDelimiters(CapabilitiesResponse, "<challenge>", "</challenge>"),
    Iterations = Text.BetweenDelimiters(CapabilitiesResponse, "<iterations>", "</iterations>"),
    Salt = Text.BetweenDelimiters(CapabilitiesResponse, "<salt>", "</salt>"),
    
    // Encrypt password using params
    ...

Alas, the crucial part of being able to read the session cookie from the login response isn’t possible. Even if we could, we wouldn’t be able to then set that cookie on the next request for the data. The M libraries you have access to in this context simply do not have the methods to do so.

Using Python

Such a script can easily be written in Python, including the cookie reading/setting part. And Power Query has a Python connector!

You would think that it’d then be as simple as writing the script in VSCode and when completed, copy/pasting it in the connector’s configuration.

&ldquo;Python connector&rdquo;
Copy/pasting the working script in the Python connector

It works… but only locally (so far)!

Once uploaded to the Power BI Service, your dataset won’t be able to run that Python script. Microsoft curates the connectors that it’ll run on its servers, and it doesn’t want to run arbitrary scripts. For that, you need to provide your own resource to run the script on and feed the data back to Power BI Service.

This is achieved by installing the Microsoft On-Premises Data Gateway on a computer. By installing this app and logging in to it with an O365 organizational account (yours for example), the app will automatically be connected to Power BI Service, i.e., the service will be aware that this computer exists and can be used to defer the running of scripts to (and other things).

  • Install Microsoft On-Premises Data Gateway on a computer that will be online any time the report will be refreshed
  • As part of the installation, login with an O365 account belonging to the same organization as Power BI Service is used with
  • In Power BI, go to your report dataset’s settings
  • Expand the “Gateway connection” section. Your newly installed gateway should be displayed here.
  • Make sure the radio button is selected and click Apply

Gateway connection in Power BI Service
Your gateway should show up automatically. Select it and Apply!

Now you can trigger a refresh of your report to confirm that the Python data source works. If you run into errors, make sure the machine you installed the data gateway on has Python installed, as well as any dependencies your script uses!

Conclusion

This post touched on two interesting topics:

  • The use of Python to craft unique data sources
  • The need for a data gateway to support the running of a Python script in Power BI Service

Note that when working on your report locally, the data gateway will never be involved; it’ll just run the script using your own machine’s Python installation.

More reading

What is an on-premises data gateway? (learn.microsoft.com)