dates

[JeffGrant]

HI all,

at the moment, I run queries that look like this everyday. (except for the API Key)

= Csv.Document(Web.Contents("https://old.puntingform.com.au/api/formdataservice/GetTrainerStrikeRateData/12-Jan-2023?ApiKey=XXXXXXXXXXXXXXXX"),[Delimiter=","])

The only part that changes is the date just before the API key. (in Red and Bold)

How can I set the query up so that the data is automatically changed to todays date without me having to edit every query?

Thaks for yoru help.

 

[Micron]

Substitute Date or Date() ?

 

[JeffGrant]

Thanks Micron, that is great for VBA, but not M code.

Anyway, I have got this far:

//fromDate = """" & DateTime.Date(DateTime.LocalNow()) & """",

fromDate = "12-Jan-2023",
Source = Csv.Document(Web.Contents("https://old.puntingform.com.au/api/formdataservice/GetTrainerStrikeRateData/" & fromDate &

I can get the "fromDate" variable to substitute ok, but I cant get the

DateTime.Date(DateTime.LocalNow()

syntax to pass as a string. I know I am missing something very simple. I have tried the DateTime.ToText function, but alas, I am not familiar enough with m code to work it out.

I am sure it has to do with the number of double quotes being used.

Thanks for your help

 

[Micron]

Sorry, missed that this was for something else. Usually quoted double quotes ( """" ) is for when you want to encapsulate something like zls ( "" ) within quotes.
Three ( """ ) is for when you want to encapsulate one double-quote ( " ).
As for the rest, I know nothing about PowerQuery. If you assign a value to a string variable, I don't suppose you can output it as you would in vba in the immediate window. Seeing what it actually holds is often valuable in troubleshooting.
HTH

 

[JeffGrant]

Solved....After a few hours of trial and error, I finaly got the URL to:

myURL = "https://old.puntingform.com.au/api/formdataservice/GetTrainerStrikeRateData/" &

DateTime.ToText(DateTime.FixedLocalNow(),"dd-MM-yyyy")

& "?ApiKey=XXXXXXX",

All solved now....