Getting new data from a REST API including user input

[AlexanderDatapa]

Hello! I'm a programmer, but pretty new to Power BI and I'm having trouble retrieving 'new' data from a REST API.

I've been testing with a free API (Agify) for a proof of concept. I've managed to make a report that can retrieve information from the API through Get data --> Web. There, I set up the URL with a parameter called name:

The results from the API can now be shown in the report:

I can change the value of the name-parameter through 'Edit query' and the report updates, but I would like to be able for users to have text input inside the report, where they can type the name, press enter or a button, and then the API data gets reloaded into the report.

It feels like that should be fairly straightforward, but I can't figure it out. I've looked online, but it either isn't possible with Power BI or I can't find the right search terms.

It does look like Power Automate may offer a solution, but that seems pretty complicated and I haven't found a good guide for this specific useage.

Is this even possible without Power Automate?
If not, can someone recommend a good guide on how to achieve this?

Thanks!

[v-tsaipranay]

Hi @AlexanderDatapa ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @AlexanderDatapa ,

Thank you for reaching out to the Microsoft Fabric Community. Also thank you @pbiuseruk  for your inputs.

To achieve dynamic user input and refresh data in Power BI, here are the options you can consider:

• If your API supports Direct Query, you can set the data source to Direct Query mode. This will allow real-time updates based on user input (e.g., name), without needing to manually refresh the data.
• You can create a parameter in Power Query (within Import Mode) to let users input a name, but the data won’t refresh dynamically. Users would need to manually refresh or edit the query to update the data.
• Embedding a Power App within the Power BI report allows users to input data dynamically, which can then trigger an API call and update the report. This approach is user-friendly but requires familiarity with Power Apps.
• While Power Automate can help trigger data updates based on user input, it adds complexity and may not be ideal if you’re looking for a simpler solution.

If Direct Query is feasible, that would be the most seamless approach. Otherwise, Power Query parameters or Power Apps would be good alternatives.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[AlexanderDatapa]

Thanks for your reply.

The Direct Query and PowerApp solutions seem most feasible.

I will look up more information on both. Though, the Direct Query approach with an API seems to be aimed at querying Azure databases, not REST APIs. 

If anyone can point to a guide for either approach, it'll be very welcome.

I'll update this post when I find a solution.

(Apologies for the late response, I got the flu and was out of the running for a while.)

[v-tsaipranay]

Hello @AlexanderDatapa ,

Thank you for your response.

You're correct, Direct Query is typically supported for structured data sources such as Azure SQL Database, SQL Server, and other similar relational systems. Unfortunately, REST APIs are not supported as Direct Query sources in Power BI, meaning they can only be accessed through Import Mode in Power Query.

This mode does not support real-time interaction and requires manual refreshes, which limits dynamic input scenarios. Given these constraints, embedding a Power App within your Power BI report is the most practical and user-friendly solution.

This approach allows report viewers to input data such as a name directly into the report interface, trigger a REST API call using that input, and view the resulting data dynamically. The integration enables live user interaction, dynamic API triggering, and the ability to store or surface the API’s output through a connected data source such as Dataverse, SharePoint, or Azure SQL, depending on your setup.

Please refer to the following documentation for you better understanding:

Power Apps visual for Power BI - Power Apps | Microsoft Learn

Hope this will help to get some idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @AlexanderDatapa  ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @AlexanderDatapa ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[pbiuseruk]

Hi Alexander,

I don't think there's a way that you can take a user input from the report section and then change the data that's being pulled automatically while using import mode.

You can go back into the data source settings and then get the users to edit the parameters there but that probably isn't the best UX.

You could potentially make a dropdown field which allows the different parameter options you're expecting and then run a Power Automate flow from within the Power BI report, which goes and makes an API call with those specific parameters. The only issue with this is that I'm not sure if you can update the semantic model or a dataflow's data with this approach. You may need to load the data from the API call into a DB or other data store and then trigger a dataflow or semantic model refresh from within that same Power Automate flow.

Alternatively to this whole approach, you could experiment with trying to use Direct Query instead of import mode but you'll need to use a connector which supports Direct Query mode. Direct Query mode works by refreshing the data at runtime as opposed to Import Mode, which refreshes the data on a schedule or when a user manually refreshes the dataset.

Hope this helps.

[AlexanderDatapa]

Thank you for your reply. I will be looking into Direct Query mode, though I'm not sure how to use it with a REST API in stead of an Azure database.

(Apologies for the late response, I got the flu and was out of the running for a while.)

[pbiuseruk]

Have a look at this blog: https://blog.crossjoin.co.uk/2021/04/25/can-i-build-a-power-bi-directquery-dataset-on-top-of-a-rest-...

I think generally, you need a Cloud DB or an ODBC Driver to use Direct Query. The blog mentions to potentially use some 3rd party tools but to be honest, it would be more cost effective to just provision your own DB within Azure and set up an Azure Data Factory Pipeline to ingest the data into the Azure DB.

As a separate note, unless there's a reason not to do this - I'd actually just use import mode instead of direct query and bring in all of the data through the API. Then once you have all of the data, I'd add slicers so that users can filter the whole dataset to just what they want. If security is a concern (e.g. user A shouldn't be able to see user B's records), then I'd make roles and apply RLS to restrict data to users based on their roles or to data which is specific to them.