Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
Anyway, this was not helpful. How I solved this:
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.
Gen AI?
Anyway, this was not helpful. How I solved this:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
121 | |
109 | |
81 | |
67 | |
67 |