To change date in Qurey Editor -To Dynamic Web link as per the computer date

[murthysydney]

Hi All,

I am extracting data from a company web link- to Power BI---- https://CJJJXXweb.amsys-prod.trainapps.apxn.au/Default.aspx?Group=3&Status=1&DateFrom=20/12/2018&DateTo=20/11/2019..

I need to copy the link every day to update the data, is the any way to make the date Dynamic as per today.

For Eg-- 20/11/2019 - to be changed as today's date automatically as per computer date.

Your suggestion will help me avoid repeated chaning.

 

[sandy666]

I am not sure what are you trying to do (link doesn't work) but maybe Date.From(DateTime.LocalNow()) will help

 

[murthysydney]

I am not sure what are you trying to do (link doesn't work) but maybe Date.From(DateTime.LocalNow()) will help

As i said its a company webapplication which cannot be accessed externally , (Date.From(DateTime.LocalNow()) where it needs to be replaced .

 

[sandy666]

post first line of your M-code (after let) - hope this is not classified

use CODE tags

 

[murthysydney]

post first line of your M-code (after let) - hope this is not classified

use CODE tags

HI Mate the below is the M Code.. The Date To - Highligted in RED needs to be dyanamic as per the computer Date ..which can be hard coded.


let
Source = Web.Page(Web.Contents("https://cXXXXX.amsys-prod.trainapps.XXX.au/Default.aspx?Group=3&Status=1&DateFrom=20/12/2018&DateTo=01/12/2019")),
Data5 = Source{5}[Data],

#"Changed Type" = Table.TransformColumnTypes(Data5,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}})

in

#"Changed Type"

 

[sandy666]

Source = Web.Page(Web.Contents("https://cXXXXX.amsys-prod.trainapps.XXX.au/Default.aspx?Group=3&Status=1&DateFrom=20/12/2018&DateTo="&Date.From(DateTime.LocalNow())&"")),

whole url is a text so to insert variable you need break url with "&...&"
Date.From(DateTime.LocalNow()) == TODAY() = today's date: 20/12/2019

hope it will work for you

 

[murthysydney]

1/12/201

 

[murthysydney]

Hi Thanks
I have changed to
Source = Web.Page(Web.Contents("https://cmXXXXX.amsys-prod.railapps.s.au/Default.aspx?Group=3&Status=1&DateFrom=2/12/2018&DateTo="&Date.From(DateTime.LocalNow())&"")),

The below error occurs __

Expression.Error: We cannot apply operator & to types Text and Date.
Details:
Operator=&

Left=https://cmXXXX.amsys-prod.railapps.s.au/Default.aspx?Group=3&Status=1&DateFrom=2/12/2018&DateTo=
Right=20/12/2019

 

[sandy666]

try

Source = Web.Page(Web.Contents("https://cmXXXXX.amsys-prod.railapps.s.au/Default.aspx?Group=3&Status=1&DateFrom=2/12/2018&DateTo="&Text.From(Date.From(DateTime.LocalNow()))&"")),

 

[sandy666]

or

let
    TT = Text.From(Date.From(DateTime.LocalNow())),
    Source = Web.Page(Web.Contents("https://cXXXXX.amsys-prod.trainapps.XXX.au/Default.aspx?Group=3&Status=1&DateFrom=20/12/2018&DateTo="&(TT)&"")),
    (rest of the code)