Forum Discussion
Change date in JSON Source string
Hello,
I'm trying to insert an updated date using either a parameter or Todays date into the string of JSON source API for the end_date= section. I've tried various solutions I've found but keep getting various errors. Here is the original source code:
Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date=2024-11-01&daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]])),
Here is one of my updated attempts using a parameter. What is wrong with my syntax:
Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date="&#"Weather Date Parameter"&"daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]])),
#"Converted to Table" = Table.FromRecords({Source}),
Currently this is the error for above version:
DataSource.Error: Web.Contents failed to get contents from 'https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date=2025-01-01daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://archive-api.open-meteo.com/v1/archive
Url=https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date=2025-01-01daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago
I've also tried inserting just the date formatted as I prefer which is YYYY-DD-MM and basically get same error:
= Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date="&Text.From(Date.Year(DateTime.LocalNow())) & "-" & Text.End("0" & Text.From(Date.Day(DateTime.LocalNow())),2) & "-" & Text.End("0" & Text.From(Date.Month(DateTime.LocalNow())),2)&"daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]]))
Two minutes after I posted this I had a thought and realized my error. I needed a second amperstand after the parameter before the rest of the string. Correct source code is (blue is the updated code):
Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date="&#"Weather Date Parameter"&"&daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]])),
#"Converted to Table" = Table.FromRecords({Source}),Alternatively this works too without parameters by dynamic Today's date:
Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date="&Text.From(Date.Year(DateTime.LocalNow())) & "-" & Text.End("0" & Text.From(Date.Day(DateTime.LocalNow())),2) & "-" & Text.End("0" & Text.From(Date.Month(DateTime.LocalNow())),2)&"&daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]])),
1 Reply
Two minutes after I posted this I had a thought and realized my error. I needed a second amperstand after the parameter before the rest of the string. Correct source code is (blue is the updated code):
Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date="&#"Weather Date Parameter"&"&daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]])),
#"Converted to Table" = Table.FromRecords({Source}),Alternatively this works too without parameters by dynamic Today's date:
Source = Json.Document(Web.Contents("https://archive-api.open-meteo.com/v1/archive?latitude=44.98&longitude=-93.2638&start_date=2010-01-01&end_date="&Text.From(Date.Year(DateTime.LocalNow())) & "-" & Text.End("0" & Text.From(Date.Day(DateTime.LocalNow())),2) & "-" & Text.End("0" & Text.From(Date.Month(DateTime.LocalNow())),2)&"&daily=temperature_2m_max,temperature_2m_min,temperature_2m_mean,apparent_temperature_max,apparent_temperature_min,apparent_temperature_mean,sunrise,sunset,precipitation_sum,precipitation_hours,windspeed_10m_max,windgusts_10m_max,winddirection_10m_dominant&temperature_unit=fahrenheit&windspeed_unit=mph&precipitation_unit=inch&timezone=America%2FChicago", [Headers=[#"Accept-Encoding"="gzip"]])),