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!

AlB's avatar
AlB
Community Champion
3 years ago

Hi Thasos 

I don't understand this part:

However, as this is a function with a parameter, PowerBI can not automatically refresh this data. And because that is necessary for the report, I can't use this solution.

and therefore i don't get where the problem is.

What function and what parameter? Can you elaborate?

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

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!