Directly query API with parameter

[zenisekd]

Hi, 

I am sort of new to using API with PBI and my boss asked me if it is possible to facilitate in Power BI the following scenario:

We use API to connect to tables from accounting software. My colleague would like to see stock levels on a certain date in the reports. At the moment, I can get the stock levels with API to a certain date through the web connector. However, my colleague wants to be able to select any date in the current year, which would mean that I have to import current stock levels with API every day in the year, which doesn't seem to me like the best idea. 

For this reason, I was wondering about the following:  is it somehow possible to use some kind of "direct query" with API so that whenever the report page was opened, an API call was made? Also, would it be possible to to somehow create a parameter in the report, so I could make a slicer selection (of date) so the API was adjusted to this date? 

Alternatively, I would like to know, what is the best practice in this field of work... Say that you have 10.000 unique products. What would be the best way to report on their stock level from API? Should I look for the stock movement table and calculate the levels or should I rather pull all the levels to dates? 

Cheers!

[Anonymous]

Hi @zenisekd ,

I hope you find these articles below helpful:

Solved: Re: Dynamic connection to Web URL - Microsoft Fabric Community

Solved: Parameters for JSON API data - Maintain Query dail... - Microsoft Fabric Community

Use Parameters & Custom Functions To Call APIs In Power BI

Best regards,

Joyce

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

[zenisekd]

Hello Joyce,

I went through the posts but did not find an answer to my key question - how do I create a direct query to a web content / API.  The posts only talk about creating the parameters in the power query, as far as I can tell.

However, I need to have a slicer in the app, which will influence the parameter on the source query and immediately call a new API query returning the data I need.

So far I was just able to find, how to create a parameter, that can be used in the Power BI desktop report view (not just power query) by the user. https://www.youtube.com/watch?v=rFI6pc94WdM&ab_channel=BIConsultingPro

It is the same as Farhad88 asked (without any answer) in https://community.fabric.microsoft.com/t5/Desktop/Dynamic-connection-to-Web-URL/m-p/346607#M155589.

UPDATE: according to Chris Webb, it is not almost impossible to create a direct query on API...: https://blog.crossjoin.co.uk/2021/04/25/can-i-build-a-power-bi-directquery-dataset-on-top-of-a-rest-...