REST API with secret email key

Hi MarcusR1 ,

Please try below steps:

1. Send the POST request to retrieve the JWT and 6-digit code.
2. Use the JWT or code to authenticate the user.
3. Return the API response as a dataframe.

Steps to Implement:

1. Enable Python in Power BI
   Go to File > Options and settings > Options > Python scripting and set up your Python environment.

2. Write the Python Script in Power Query:

   • Go to Home > Transform Data > Transform > Run Python Script and paste the following:

     import requests
     import pandas as pd
     
     # Step 1: Get JWT and 6-digit code
     auth_url = "https://api.domain.com/v0/auth/code"
     headers = {"Content-Type": "application/json"}
     payload = {"email": "[email protected]"}
     
     auth_response = requests.post(auth_url, json=payload, headers=headers)
     
     if auth_response.status_code == 200:
         auth_data = auth_response.json()
         jwt_token = auth_data.get("jwt")  # Assuming JWT is returned
         auth_code = auth_data.get("code")  # Assuming 6-digit code is returned
     
         # Step 2: Use JWT or code to authenticate
         api_url = "https://api.domain.com/v0/protected_endpoint"
         headers["Authorization"] = f"Bearer {jwt_token}"
         payload = {"code": auth_code, "email": "[email protected]"}
     
         api_response = requests.post(api_url, json=payload, headers=headers)
     
         if api_response.status_code == 200:
             data = api_response.json()
             df = pd.DataFrame(data)  # Convert to DataFrame for Power BI
         else:
             df = pd.DataFrame([{"error": "Failed to authenticate"}])
     else:
         df = pd.DataFrame([{"error": "Failed to fetch auth data"}])
     
     df

3. Load Data in Power BI

• After running the script, Power BI will convert the output to a table.
• Click Close & Apply to load the data into your report.

Please mark this as solution if it helps you. Appreciate Kudos.