Inject choosen date into an API

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.

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