Generate a JSON Web Token (JWT) in PowerBI/PowerQuery

[heriberto_mb]

I'm trying to generate a JSON Web Token (JWT) inside powerBI to generate a github api report.

The following command generates the JWT:

python "generate jwt.py" power-bi-reporting.2023-09-21.private-key.pem <app_id>

This returns a JWT which is used later to generate a github api token. I'm wondering if there's a way to incorporate the python execution into PowerQuery/PowerBI to generate the token as a first step, or if there is another method to generate it.

This is the body of "generate jwt.py":

#!/usr/bin/env python3
import jwt
import time
import sys

# Get PEM file path
if len(sys.argv) > 1:
pem = sys.argv[1]
else:
pem = input("Enter path of private PEM file: ")

# Get the App ID
if len(sys.argv) > 2:
app_id = sys.argv[2]
else:
app_id = input("Enter your APP ID: ")

# Open PEM
with open(pem, 'rb') as pem_file:
signing_key = jwt.jwk_from_pem(pem_file.read())

payload = {
# Issued at time
'iat': int(time.time()),
# JWT expiration time (10 minutes maximum)
'exp': int(time.time()) + 600,
# GitHub App's identifier
'iss': app_id
}

# Create JWT
jwt_instance = jwt.JWT()
encoded_jwt = jwt_instance.encode(payload, signing_key, alg='RS256')

print(f"JWT: {encoded_jwt}")

[v-junyant-msft]

Hi @heriberto_mb ,

First you need to enable Python in Power BI. Go to 'File', choose 'Options and settings' -> 'Options', select 'Python scripting' from the left pane, and specify the path to your Python installation.

And then in Power Query, you can invoke your Python script. However, it's important to note that directly executing scripts that generate JWTs and interact with external APIs might require additional steps or workarounds, as Power Query primarily focuses on data transformation and loading.

And another approach might be to generate the JWT outside of Power BI and then import it as a parameter or directly within your query to authenticate against the GitHub API. This method would involve running your Python script externally and using the output token within Power BI.

If you're looking to automate or streamline this process entirely within Power BI, you might need to explore custom connectors or invoking web services with pre-generated tokens.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.