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.

 

[murthysydney]

Thank You --It worked ..

 

[sandy666]

You are welcome
Thanks for the feedback

 

[murthysydney]

Hi All,
I am using the table with the web links to extract data from the web below is the query editor.

The data when I extract is not picking up from 18/01/2000 it's just picking up for 1 year and doesn't show any error.
I am using TT for until today.
Number to text for group data.

Is there any mistake why this is not picking data from 18/01/2000
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(year as number) as table=>

let

TT= Text.From(Date.From(DateTime.LocalNow())),
Source = Web.Page(Web.Contents("https://cmweb.amsys-prod.railapps.XXXXXXBBBBXXXX.au/Default.aspx?Group="&Number.ToText(year)&"&Status=0&DateFrom=18/01/2000&DateTo="&TT&"")),
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}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}})
in
#"Changed Type"
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks

 

[sandy666]

where is the (year) definition?
what is the value of the (year) in case above?
why Number.ToText() ?

 

[murthysydney]

Year Definition is in the Top --

(year as number) as table=>​

Value of the year is = 3 or 4 or 5 or 6 based on the web link which changes based on the department.​

Number.To Text () .. is to convert

3 or 4 or 5 or 6 not sure whats in the web link so changed to text.i
I am using a custom colum to get data when i get the data

(year as number) as table=>​

let

TT= Text.From(Date.From(DateTime.LocalNow())),
Source = Web.Page(Web.Contents("​

https://cmweb.amsys-prod.railapps.XXXXXXBBBBXXXX.au/Default.aspx?Group=​

"&Number.ToText(year)&"&Status=0&DateFrom=18/01/2000&DateTo="&TT&"")),
Data5 = Source{5}[Data],​

 

[sandy666]

is this just the code you posted without tags [CODE] your M-code should be here! [/CODE]
this not so hard to use CODE tags

check differences (if any) between working / not working urls

what's kind of error (if any) you got?

 

[sandy666]

your RangeStart is 29/09/2019 so maybe this

I have no idea without whole code, working URL and with such sparse information, sorry