Inject choosen date into an API

[Pigsfoot]

HI,

I have an Web based API where I currently include fixed dates in Epoch format. The API looks something like this ( sections redacted for security )

Source = Json.Document(Web.Contents("
https://******/api/*******/venue/4/events/hourly-visitors"
& "?comparison_date=1720656000&event_date=1721260800&from_hour=17&to_hour=19" & "&zone_ids=159,165", [Headers=[Authorization="Bearer *******************************"]])),

Is it possible to allow the end user to select a date from a date picker that is then inserted into the API string to get the required data rather than me fixing it and having to craete a new query every time someone wants data for a different date ?

[shafiz_p]

Hi @Pigsfoot ,

I think in import mode such scenario is not possible to implement. For Direct query mode this is possible.

You can change parameter value in power bi desktop using edit parameter option or in power bi service setting option for import query mode.

If you interested to use edit parameter for import query option, then in power query create parameter and replace hardcoded date with the parameter. 

First create a date query using list.date funtion. Chk image:

Then Create parameter, for my case startdate. Check image:

For your case you need to convert date to Epoch Format. 

Replace hard code date with the parameter. Check image:

Close and load. In power Bi Desktop, change date through edit parameter option. Check image:

Select any date you want your souce to update. Check image:

Similarly, in power bi service through setting option:

Go to parameter to change value:

Hope this helps!!
If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[Swapnilmandloi]

Yes, it is possible to allow end users to select a date using a date picker in Power BI, and then dynamically insert the selected date into the API string.

Here’s how you can achieve this:

Steps:

1. Add a Date Picker:

   • In Power BI, you can use a slicer as a date picker for users to select a specific date.
   • Create a table with a range of dates, which can be done using the following DAX expression:
     DAX

     DateTable = CALENDAR(DATE(2023,1,1), DATE(2030,12,31))
   • Use this table in a slicer so that the user can pick a date.

2. Convert the Selected Date to Epoch Format:

   • You can create a measure or a column to convert the selected date into Epoch format. You can use DAX for this:
     DAX

     SelectedDateEpoch = DATEDIFF(DATE(1970, 1, 1), SELECTEDVALUE(DateTable[Date]), SECOND)

3. Modify the Power Query to Use the Selected Date:

   • In Power Query, you’ll need to pass this Epoch value into your API string. This can be done by creating a parameter that stores the Epoch value and then dynamically updating the API URL.

   Here’s an outline of the changes:

   • In Power Query:
     1. Create a parameter in Power Query that accepts the Epoch date value (you can set this as a blank or initial value).
     2. Modify your API query to use this parameter instead of a fixed date:
        M

        let SelectedEpoch = #date parameter here#, Source = Json.Document(Web.Contents("https://******/api/*******/venue/4/events/hourly-visitors" & "?comparison_date=" & Text.From(SelectedEpoch) & "&event_date=" & Text.From(SelectedEpoch) & "&from_hour=17&to_hour=19" & "&zone_ids=159,165", [Headers=[Authorization="Bearer *******************************"]])) in Source

4. Pass the Selected Date to the Parameter:

   • Link the selected date from the date picker slicer to the parameter in Power Query. You might need to use a combination of DAX and Power Query to achieve this dynamic linking.

Benefits:

• The user can choose any date from the slicer.
• The Epoch date gets automatically calculated and inserted into the API request.
• You won't need to hard-code dates or create a new query for each new request.

[Pigsfoot]

@SwapnilmandloiThank you for the reply. I have bene trying to get this to work but i'm stuck on point 4.

Can you expand at all on how i link the date from the new date picker to the parameter? Thanks

[Anonymous]

Your solution is great shafiz_p 

Hi, @Pigsfoot 

You should follow shafiz_p's method to practice it. When a user changes this parameter in Power BI Service it will update your data loaded into Power BI. You cannot use the slicer to perform this operation because the slicer uses columns that are already loaded into the model and you try to pass the parameter before loading.
Here are some cases where a similar issue has been discussed before:

Solved: Pass parameters dynamically from Power BI to REST ... - Microsoft Fabric Community

Here are some examples of using this method:

Parameterize an API request in Power Query – Wise Data Decisions

This example is the same as the one shown by shafiz_p.

Best Regards

Jianpeng Li

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