Define a variable in M query and concatenate to form url

[manirohit]

Hi All,

Need help in defining a date time varibale in M query. I have an API which uses from date and to date as query parameter. I want to pass current date time in the to date and yesterday date time in from date.

I somehow defined the datetime like below

fromdate = Date.AddDays(Date.From(DateTime.FixedLocalNow()),-1),
todate = DateTime.FixedLocalNow(),

but i need to pass these values in the URL call, How can I use above parameters to form the API URL?  

auth_url = "https://abc.com/alldata?fromDate=&toDate=",

Let me know if there is another way of doing it or if possible pick the from date and to date values from some other table.

Thanks in advance.

[petrovnikitamai]

i guess

auth_url = "https://abc.com/alldata?fromDate='"&fromdate&"'toDate="

i'm no sure but try to use this code where "timezone", "start_dtime", "end_dtime" is text parametr

= Odbc.Query(source, "select shift_id,
start_dtime+'"&timezone&"' as start_dtime, 
end_dtime+'"&timezone&"' as end_dtime 
from shifts_segment 
where start_dtime>='"&start_date&"' and end_dtime<='"&end_date&"'")

for example, start_dtime format is text 2017-08-01 00:00:00 

[manirohit · Accepted solution]

Thanks @petrovnikitamai

I was able to achieve this from below. so basically the concatenation works with '&' and also the type should be text

fromdate = Date.AddDays(DateTime.From(DateTime.FixedLocalNow()),-1),
    todate = DateTime.FixedLocalNow(),
    fromDate = DateTime.ToText(fromdate),
    toDate = DateTime.ToText(todate),
    auth_url = "https://abc.com/alldata?fromDate="&fromDate&"&toDate="&toDate,