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.