REST API with secret email key

[MarcusR1]

Hi,

I have an POST API call that sends an email, in the content of that email is a link containing a direct login link  with a JWT (app.domain.com/auth/login/code/bWFyY3VzLnJvc2tpbGxA.......) , and also a 6 digit aphanumeric code that is used by the app to create a bearer token (api.domain.com/v0/auth/code) taking the users email address and the 6 digit code as body data

How can I feed the 6 digit code or the JWT back to the query 

Using = Json.Document(Web.Contents(URL2,[Headers=[#"Content-Type"="application/json; charset=utf-8"],Content=Text.ToBinary(Body2)])) I get a 500 error, so i modified it to use a Post action

= Json.Document(WebAction.Request(WebMethod.Post,URL2,[Headers=[#"Content-Type"="application/json; charset=utf-8"],Content=Text.ToBinary(Body2)]))

but I get "Expression.Error: We haven't been given permission to perform actions against this resource."

is there a better way to do this, supported by PowerBI?

Thanks

[FarhanJeelani]

Hi @MarcusR1 ,

Your issue seems to be related to authentication and Power BI service limitations when calling REST APIs.

Try using Power Automate:

Power BI does not support direct API calls for authentication-sensitive actions, so the best way is to:

• Create a Power Automate Flow that fetches the JWT and 6-digit code.
• Use the HTTP connector in Power Automate to call the API.
• Return the token to Power BI using a Power Automate data source.

  You can also try using Web.Contents with Query Parameters:

  If you must use Power Query (Web.Contents), try structuring your API request correctly:

  let
      URL = "https://api.domain.com/v0/auth/code",
      Body = "{""email"":""user@example.com"", ""code"":""123ABC""}",
      Response = Json.Document(
          Web.Contents(URL, 
          [
              Headers = [#"Content-Type"="application/json"],
              Content = Text.ToBinary(Body)
          ]))
  in 
      Response

• If the API requires authentication, you may need to pass an "Authorization": "bearer <Token>" header.
• If you receive "Expression.Error: We havn't been given permission", try:
  • Checking data source permissions in Power BI.
  • Using a gateway if calling an external API.

Use a Custom Connector

• If Power Automate isn’t an option, consider:
• Creating a custom Power BI connector using Power Query M and an Azure Function.
• This allows better handling of authentication and API responses.

If the API call requires a user login session or secrets that Power BI doesn’t support directly, Power Automate or an external script (Azure Function, Python, etc.) is the best way to pass the JWT and 6-digit code securely.

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

[MarcusR1]

Hi @FarhanJeelani 

Thank you for your detailed response, it is disappointing that PowerBI has so many basic things that it can't do. I am trying to create a template for distribution to non-technical clients so that they can connect to our API and customize their own reports, so forcing clients to download Power Automate and fiddle around with configuration settings is not a suitable solution. Can you help me with the Python script, presumably it will be in Power Query?

[FarhanJeelani · Accepted solution]

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": "user@example.com"}
     
     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": "user@example.com"}
     
         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.

[MarcusR1]

Hi @FarhanJeelani ,
Thank you again for your detailed suggestion, this is really complicated though! 

The script doesn't work because the Authentication code is sent by email (which is completely disconnected from the script, so there is no way to pass it back in), and I can't even save the Code as a variable in Power Query because the Python script doesn't seem to recognize PQ variables. More fundamentally than that, though, is that this requires the user to install Python (and requests libraries) which is far too technical for a Power BI user who is used to just pulling in data using saved credentials (Basic Auth) or SSO - also, their IT department won't want them installing Python on their PCs. Surely, with the world moving quickly towards email verification (for security reasons) Power BI should be moving with the times and developing functions/settings that can cope with this? Thanks for trying.