Forum Discussion

MarcusR1's avatar
MarcusR1
Helper II
1 year ago
Solved

REST API with secret email key

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

 

Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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.

4 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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"":""[email protected]"", ""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's avatar
      MarcusR1
      Helper II
      1 year ago

      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's avatar
        FarhanJeelani
        Super User
        1 year ago

        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.