Creating a Report froma source with an API connect

[Walt101062]

I have a connection from Power BI to a data source via an API.

Noob question: I would like the user to be able to select the period for the report.

I'm not sure what date range the API allows, and where to establish the filter. In other words, is it standard practise to connect via the API and have a simple filter on the report, or do I somehow filter the data I bring through via the API only the data that dalls within the date range the user wishes to see?

[v-pnaroju-msft]

Hi Walt101062,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution to guide other members.

Thank you.

[v-pnaroju-msft]

Hi Walt101062,

We wanted to follow up since we haven’t heard back from you regarding your query. If you’ve figured out a solution, please share it with the community—it might really help others dealing with the same issue.

If you found any response useful, kindly mark it as the accepted solution and give kudos. This helps others find answers more easily.

Thank you.

[v-pnaroju-msft]

Thankyou, @DataNinja777, for your response.

Hi Walt101062,

We appreciate your question posted on the Microsoft Fabric Community Forum.

As per my understanding, since the API requires From and To dates, Power BI lets users enter the dates, which we can then use in the API request by creating Parameters in Power BI Desktop.

Please follow the steps below to resolve the issue:

1. In Power BI Desktop, go to Manage Parameters and create two parameters named StartDate (type: Date) and EndDate (type: Date).
2. Modify the API URL in Power Query and include these parameters in the API URL as shown in the example.
   

   let

       StartDateText = Date.ToText(StartDate, "yyyy-MM-dd"),

       EndDateText = Date.ToText(EndDate, "yyyy-MM-dd"),

       Source = Json.Document(Web.Contents("https://your-api-endpoint.com/data?",

           [Query = [from = StartDateText, to = EndDateText]]

       ))

   in

       Source

3. Change the parameter values before refresh. In Power BI Desktop, you can update these values directly. In Power BI Service, go to Dataset, then Settings, and then Parameters to update and refresh.

Additionally, please refer to the following links for more information:
Parameters - Power Query | Microsoft Learn
Web.Contents - PowerQuery M | Microsoft Learn

If you find our response useful, kindly mark it as the accepted solution and give kudos. This will help other community members who have similar questions.

If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[DataNinja777]

Hi @Walt101062 ,

When connecting Power BI to a data source via an API, you've encountered a common decision point regarding how to let users filter a report by a specific period. The standard practice and best approach depend heavily on the API's capabilities and the volume of your data. There are two primary methods: filtering the data after it has been loaded into Power BI or filtering the data at the source by modifying the API call itself.

Filtering within the Power BI report is the more straightforward option. In this scenario, you would configure your API connection to pull in a large, comprehensive dataset. Once the data is loaded into your Power BI model, you would add a slicer visual to your report canvas. By dragging your date field into this slicer, you provide users with an interactive control to select the desired date range. Power BI then filters the data that has already been imported. This method is perfectly suitable for small to moderate datasets or when an API does not offer its own filtering options. However, for very large datasets, this approach can become inefficient, leading to slow report refreshes and high memory consumption, as your machine has to process the entire dataset.

The more efficient and scalable method is to filter the data at the source by passing parameters through the API call. This is considered the industry best practice. This technique involves asking the API to send only the data that falls within the user-specified date range, which significantly reduces the amount of data transferred and processed. To implement this, your first and most crucial step is to consult the API's documentation. You need to look for sections on "parameters" or "filtering" to see if it supports date-based queries, often using parameter names like start_date, end_date, from, or to. The documentation will also specify the required date format. If the API supports these parameters, you can create dynamic parameters within Power BI's Power Query Editor, perhaps named StartDate and EndDate. You would then edit the Source step of your query, using the advanced editor to append the API's date parameters to the URL and link them to the Power BI parameters you created. This way, Power BI only requests the specific slice of data needed, resulting in much faster performance and a more responsive report, especially as your data volume grows.

Best regards,

[Walt101062]

Thanks for your reply. I have noticed that the API does require From and To dates, as part of its format. Obviously I can set those dates up as parameters or hard code them directly within the code. Is is there any way to use Power BI to allow the user to enter the dates, and then to use those dates entered as the From and To dates in the API?