dynamic API call depending on selected project

[Anonymous]

I would like to call an API dynamically depending on which project is called (via slicer). Via a parameter I can control the M-code so that the API is called correctly. However, I would like the parameter to be automatically filled depending on which project you select. Is this somehow possible?

[Anonymous]

Hi @Daryl-Lynch-Bzy thanks for your quick message. the API call works flexibly depending on what you put in the parameter. My question now is if I can populate the parameter dynamically depending on what the user selects. I have seen that this works for DirectQuery via "Bind Paramter". However, my source is a REST API and I can't set the storage mode to DirectQuery. Is there any other way to implement this?

[Daryl-Lynch-Bzy]

Hi @Anonymous - you are correct - Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn will not work if you cannot set the data source to Direct Query.  

If you have a limited number of users accessing the data, you may consider Power App that allows the user define the parameter, then trigger a Flow to update parameter and refresh the dataset.  I think you need to use the Power BI Rest API for this as Power Apps and Power Automate connectors don't have update parameter feature. 

Datasets - Update Parameters In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Note that depending on the capacity you may be limited by the number of changes that a user can make in 24 hour period.

[Daryl-Lynch-Bzy]

Hi @Anonymous - to make the query dynamic using a M parameter you need to use a Relative Path reference to call the API.  Please see the example in the Power Query documentation.  Web.Contents - PowerQuery M | Microsoft Learn.  So, instead of creating a single string with the full URL path, you need to break into BaseUrl, the relativepath and query.  Something like this:

BaseUrl = "https://xxxxxx/api",
RelativePath = "xxxx" & Parameter,
Query = "xxxx"