I created a Fabric Dataflow Gen2 Web API connection to retrieve csv file data from an Environment Canada daily environmental data web API.
The API provides parameters for specific date ranges eg start and end dates.
I wanted to get data for a date range that had a fixed start date but dynamic end date = current date.
An example of the url for hard-coded date range is:
https://api.weather.gc.ca/collections/climate-daily/items?datetime=2013-01-01%2000:00:00/2023-06-11%2000:00:00&STN_ID=51558&sortby=PROVINCE_CODE,STN_ID,LOCAL_DATE&f=csv&limit=150000&startindex=0
In this example I want to replace the “2023-06-11” with a dynamic current date.
This turned out to be relatively simple. The steps to do this are provided below.
Create a new Dataflow Gen 2 dataflow.
Choose the Web API connector.
In the Web API connector settings the url field is empty.
Enter the full hardcoded url eg from my example above. (We will edit the url later to include a dynamic end date.) Then click Next to retrieve the data.
Preview the data to make sure that the connector works. Then click Next.
You can then see the dataflow query steps and note that the Source is our hardcoded url. You can edit the query Source to include a dynamic end date.
Instead of the hardcoded end date use DateTime.LocalNow() formatted to match the Web API parameter requirements.
https://api.weather.gc.ca/collections/climate-daily/items?datetime=2013-01-01%2000:00:00/” & Date.ToText(Date.From(DateTime.LocalNow()),”YYYY-MM-DD”) & “%2000:00:00&STN_ID=51558&sortby=PROVINCE_CODE,STN_ID,LOCAL_DATE&f=csv&limit=150000&startindex=0
Replace the hardcoded url with this new dynamic url. Preview that the connector is still working with the edits and click Next.
That is it. Now when you refresh your dataflow it will retrieve an updated range of data.
