Forum Discussion

misaki's avatar
misaki
Regular Visitor
7 months ago
Solved

sending API requests from Power Query

When sending API requests from Power Query:
If I use the Web connector and store credentials in the credential store, I am unable to include a Content body in the Web.Contents request.
It appears that Power BI blocks requests that contain Content when credentials are managed by the credential store.

 

My question is:
How can I safely keep the credentials protected while still being able to send an API request that contains a Content body?

Is there a recommended or supported way to securely authenticate and include Content in Web.Contents requests?

Need Help
  • v-lgarikapat's avatar
    v-lgarikapat
    7 months ago

    Hi misaki ,

     

    Workaround to handle this scearion :

     

    Use query parameters or headers instead of Content

    If the API supports authentication via headers (e.g., Authorization: Bearer <token>) or query string, you can safely store credentials in the credential store and still send requests.


    m
    Web.Contents("https://api.example.com/data",
    [
    Headers = [Authorization="Bearer " & AccessToken]
    ])
    Use relative path with Web.Contents

    Power BI allows Content bodies only when the base URL is fixed and the dynamic part is passed via RelativePath or Query.

    This way, the credential store knows exactly which domain is being authenticated.

    :

    m
    Web.Contents("https://api.example.com",
    [
    RelativePath = "endpoint",
    Content = Text.ToBinary("{""param"":""value""}")
    ])
    Use OAuth2 authentication

    If the API supports OAuth2, Power BI has built-in support.

    You authenticate once, and Power BI manages tokens securely.

    This avoids embedding secrets in your query.

    Store secrets in Azure Key Vault or parameterize them

    Instead of hardcoding credentials, you can store them in Azure Key Vault or as Power BI parameters.

    Then reference them in your query, keeping sensitive values out of the M code.

     

     

     

    Thanks,

    Lakshmi.

     

     

     

     

     

     

     

     

5 Replies

  • v-lgarikapat's avatar
    v-lgarikapat
    Community Support
    7 months ago

    Hi misaki 

    Thanks for reaching out to the Fabric Community.

    To better understand your scenario, could you kindly provide more details on the following points:

     

    • What type of content are you trying to send in the request body?
    • Are there specific authentication methods supported by the API (like OAuth or API keys)?
    • What is the expected structure of your data that needs to be sent?

     

    Thanks,

    Lakshmi.

    • misaki's avatar
      misaki
      Regular Visitor
      7 months ago

      Hi v-lgarikapat 

       

      I’m sharing the full text of the request.
      The authentication method supported by the API is Basic authentication.

       

      let
      url = "https://secure.p01.eloqua.com/",
      body = "{
      ""name"": ""EmailSendActivitiesExport"",
      ""fields"": {
      ""ActivityId"": ""{{Activity.Id}}"",
      ""ActivityType"": ""{{Activity.Type}}"",
      ""ActivityDate"": ""{{Activity.CreatedAt}}"",
      },
      ""filter"": ""'{{Activity.Type}}' = 'EmailSend'"",
      ""areSystemTimestampsInUTC"": true
      }",

      Source = Web.Contents(
      url,
      [
      RelativePath = "api/bulk/2.0/activities/exports",
      Headers = [
      #"Content-Type"="application/json",
      Authorization="Basic " & Authorization
      ],
      Content = Text.ToBinary(body)
      ]
      ),
      JsonResponse = Json.Document(Source)
      in
      JsonResponse

       

      • v-lgarikapat's avatar
        v-lgarikapat
        Community Support
        7 months ago

        Hi misaki ,

         

        Workaround to handle this scearion :

         

        Use query parameters or headers instead of Content

        If the API supports authentication via headers (e.g., Authorization: Bearer <token>) or query string, you can safely store credentials in the credential store and still send requests.


        m
        Web.Contents("https://api.example.com/data",
        [
        Headers = [Authorization="Bearer " & AccessToken]
        ])
        Use relative path with Web.Contents

        Power BI allows Content bodies only when the base URL is fixed and the dynamic part is passed via RelativePath or Query.

        This way, the credential store knows exactly which domain is being authenticated.

        :

        m
        Web.Contents("https://api.example.com",
        [
        RelativePath = "endpoint",
        Content = Text.ToBinary("{""param"":""value""}")
        ])
        Use OAuth2 authentication

        If the API supports OAuth2, Power BI has built-in support.

        You authenticate once, and Power BI manages tokens securely.

        This avoids embedding secrets in your query.

        Store secrets in Azure Key Vault or parameterize them

        Instead of hardcoding credentials, you can store them in Azure Key Vault or as Power BI parameters.

        Then reference them in your query, keeping sensitive values out of the M code.

         

         

         

        Thanks,

        Lakshmi.