Multiple strings as input for API in Power BI

[Thasos]

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, and the API returns the balance of that project. I want to retrieve all balances of all projects. However, the API doesn't allow for more then 50 Project IDs in one call. Thus, I had to make strings of 50 Project IDS. So now I've a system which automatically divides all of the project ID's in strings of 50:

This then forms the input for the API, which then returnes the balance for 50 projects per StringProjectID:

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.

Does anyone happen to know a workaround, so that I can use these strings as input for the API?

Right now, the only option I see is to manually make 15 queries and just copy-paste the strings from above in it.. But as you might imagine, this is not what I want: with each new project, I have to manually update the query or make a new query if a new string has been made. If anyone has tips I would be very happy!

Thanks in advance!

[AlB]

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]

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 · Accepted solution]

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!