topic Re: Auto Update URL Data Source in Developer

Auto Update URL Data Source

Jar343 — Mon, 08 Aug 2016 11:34:53 GMT

Hi,

Im new with Power BI and have run in a issue.

The data that i use i get by using Web as datasource and then entering the URL.

The problem is that the URL contains a Begin and Endate. namely .CVS?from=2016-07-26&to=2016-07-27

My question is how do i replace these value automaticlaly with the the Current Date in to= And the day of Yesterday for from=

Re: Auto Update URL Data Source

v-chuncz-msft — Tue, 09 Aug 2016 10:21:22 GMT

Hi Jar343,

Based on my research, you could try to use Power Query Formula Language (informally known as "M") in the Advanced Editor.

let
    Source = Web.Page(Web.Contents("http://www.domain.com/xxx.CVS?from=" & DateTime.ToText(Date.AddDays(DateTime.LocalNow(), -1), "yyyy-MM-dd") & "&to=" & DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"))),
    Data0 = Source{0}[Data]
in
    Data0

Re: Auto Update URL Data Source

Jar343 — Tue, 09 Aug 2016 14:06:29 GMT

Hi Sam,

Thanks for your help it works quite good but because of the Power Query Foluma i have run in another issue.

With your code the data that is shown is only from today the -1 does not seem to do anithing i have also changed it to -4 with no effect.

I have tried changin your code to

 

from=" & DateTime.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -1), "yyyy-MM-dd") & "&to=" & DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"))),



in

    #""

But now i get the error

Expression.Error: Can not convert the value #date(2016, 8, 😎 to type DateTime.
Details:
    Value=8/8/2016
    Type=Type

Finally I also tried to do the Load from with static values but to now effect

Re: Auto Update URL Data Source

Jar343 — Wed, 10 Aug 2016 12:08:17 GMT

Hi Sam,

Yoour solution worked perfectly just had to remove the space in

(Date.From(DateTime.LocalNow()), -1),

(Date.From(DateTime.LocalNow()),-1),