Forum Discussion

Thasos's avatar
Thasos
Regular Visitor
3 years ago
Solved

Multiple strings as input for API in Power BI

Hi community,   While working on a report, I've run into some trouble. I am in the following situation: I want to retrieve the balance of a project via an API. The input for the API is a Project ID...
Need Help
  • Thasos's avatar
    Thasos
    3 years ago

    Oh wow, after (A LOT) of hours trying to figure out a solution, it seems like all I had to do was to add a 'Relative path': = Table.AddColumn(#"Rijen gegroepeerd", "Aangepast", each let
    Bron = Json.Document(Web.Contents("https://www.yanomo.com", [RelativePath="/api/v1/projects/balances?id="&[StringProjectID]]))
    in
    Bron)

     

    And now it works perfectly: the error is gone and the automated refresh is again possible!

Thasos's avatar
Thasos
Regular Visitor
3 years ago

Hi AlB,

 

My apologies, let me elaborate.

 

When I add in the colom 'Aangepast' (= Table.AddColumn(#"Rijen gegroepeerd", "Aangepast", each let
Bron = Json.Document(Web.Contents("https://www.yanomo.com:443/api/v1/projects/balances?id="&[StringProjectID]))
in
Bron)), I get the data I want. But I also get the following error in Data Source Settings:

 

(The error is: "Some data sources cannot be listed due to manually constructed queries")

 

Elsewhere I found out why I get this  error, which prevents automated refresh from working: "Queries where data access happens inside a function and where the data source is dependent on parameters to the function can't currently be refreshed. That's because we're doing static analysis of the query to discover the data source, and our static analysis can't yet handle this scenario."

 

So now I'm looking for a work around/another solution to be able to use the strings as input (parameter) for the API.

Thasos's avatar
Thasos
Regular Visitor
3 years ago

Oh wow, after (A LOT) of hours trying to figure out a solution, it seems like all I had to do was to add a 'Relative path': = Table.AddColumn(#"Rijen gegroepeerd", "Aangepast", each let
Bron = Json.Document(Web.Contents("https://www.yanomo.com", [RelativePath="/api/v1/projects/balances?id="&[StringProjectID]]))
in
Bron)

 

And now it works perfectly: the error is gone and the automated refresh is again possible!