Passing Parameter to support an API Auth Header

[shuhn1229]

Hi,

Running into issues with this... I have an query to an API that looks like this:

let
    Source = let
 url = "URL",
    body = "{""id"": ""0000""               }",
    Source = Json.Document(Web.Contents(url,[Timeout= #duration(0, 1, 0, 0),
        Headers = [#"Authorization"= ""&Token&"", #"Content-Type"="application/json"],
        Content = Text.ToBinary(body)
    ]))
in
    Source

When I hard code "&Token&" with my key, the query works fine. But I am trying to extract my key from a text file (so I can scale this process), pass the result as a Parameter called Token, so I don't have to do any hard coding, and the token is fetched from the key. To do this I use the following to get a list with one value (my token):

let
    Source = Excel.Workbook(Web.Contents("URL")){[Name="myParameter"]},
    Data = Source[Data],
    Column2 = Data[Column1]
in
    Column2

I convert this to a list so I can use the output as a parameter.

Then I go to Parameters, and search for this Query and select it, it doesnt update the current value from sole value / result of the query list above.

Am I doing something wrong here?

[AnalyticsWizard]

@shuhn1229 

Integrating a dynamic token into your Power BI data source query from an external source like a text file can streamline processes and enhance security, but it comes with certain challenges, particularly around parameterization and refreshing of the token. Based on the issues you're facing, it looks like the parameter isn't updating as expected. Here's a step-by-step method to potentially solve this:

### Step 1: Ensure Proper Setup for Reading the Token

First, make sure your method for retrieving the token from an external file is correctly set up and that the token is being correctly extracted. From your description, it seems you're trying to pull the token from an Excel file hosted online. Here's a simplified way to do this, assuming the token is stored as a single value in an Excel file:

```powerquery
let
Source = Excel.Workbook(Web.Contents("URL to Excel File"), null, true),
DataSheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
TokenValue = DataSheet{0}[Column1]
in
TokenValue
```

**Adjustments to Note:**
- Make sure `"Sheet1"` is replaced with the actual name of your sheet.
- Ensure that `{0}[Column1]` accurately points to the cell where your token is stored (first row, first column in this case).

### Step 2: Set Up the Parameter

If you're using Power BI Desktop, you can set up a parameter to hold this token value:

1. **Create a Parameter**:
- Go to `Home` > `Manage Parameters` > `New Parameter`.
- Set the parameter type to `Text`.
- Optionally, set the current value to a default token value.

2. **Linking the Parameter to the Query** (This step seems to be where you are having trouble):
- Instead of pulling the parameter value directly from the query result, consider manually updating the parameter or using a script if the update must be automated outside of Power BI’s native capabilities. Power BI does not automatically refresh the parameter’s value based on a query’s result due to potential security and performance reasons.

### Step 3: Use the Parameter in the API Query

Now you can use this parameter in your API request:

```powerquery
let
url = "Your API URL",
token = ParameterName, // Replace ParameterName with the name of your parameter
body = "{""id"": ""0000""}",
Source = Json.Document(Web.Contents(url, [
Timeout = #duration(0, 1, 0, 0),
Headers = [Authorization= "Bearer " & token, Content-Type="application/json"],
Content = Text.ToBinary(body)
]))
in
Source
```

**Note**: Replace `"Bearer "` with the appropriate prefix as required by your API; some use "Token" others use "Bearer", etc.

### Step 4: Handling Refresh Issues

When deploying to the Power BI Service:
- Ensure that the file from which the token is read is accessible. If it’s on OneDrive or a similar service, make sure Power BI can access it during refreshes.
- Consider using a gateway if the file is stored on-premises to ensure connectivity.

### Alternative Approach

As maintaining dynamic parameters for security tokens in Power BI is complex and sometimes not feasible due to security practices, consider:
- Using environment variables or secure vault services (if applicable) to handle tokens more securely.
- Automating token retrieval and update through external scripts that Power BI calls, rather than directly embedding sensitive handling within Power BI.

This method should help streamline your token management process for API queries in Power BI while adhering to best practices for security and scalability. If specific technical limitations are encountered, adjusting the approach based on available infrastructure and tools might be necessary.

[shuhn1229]

Gen AI?

[shuhn1229 · Accepted solution]

Anyway, this was not helpful. How I solved this:

• Forget the parameter, just reference a query
• ignore privacy levels in query
• create a dataflow from an excel / file and extract the first row with drilldown in query
• turn off data loads of all references of the helper query with the token
• reference the query in API call.