Combination of URL and Changeable Datetime to URL for Powerbi web request

[Anonymous]

Hi good folks

I am trying to request data from a webservice using the URL. However in the URL there is the DATE section which is encoded. I have so far succeeded to combine the part of the url with startdate and enddate and constructed the code.

However the error I am getting is as follows in the query section (Advanced Editor):

Expression.Error: We cannot convert the value "22-03-2019 12:09"  to type Record.
Detaljer:
    Value=22-03-2019 12:09
    Type=Type

The code I am using:

let
    Date = DateTime.ToText((DateTime.LocalNow()), "dd-MM-yyyy hh:mm") ,   
    Date2 = DateTime.ToText((DateTime.LocalNow()), "dd-MM-yyyy hh:mm"),
    Kilde = Csv.Document(Web.Contents("http://a website/data/glovadata/search/csv?StartTimeLocal=" + Uri.BuildQueryString(Date) + "&EndTimeLocal=" + Uri.BuildQueryString(Date2)),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Kilde

The first Date must be so i can say -1 day. I tried adding Date.from(datetime........... but didnt end well due to the first error.

Any help is appreciated

[Nolock]

Hi @Anonymous,

I've modified your query according the MS help page for Uri.BuildQueryString: https://docs.microsoft.com/en-us/powerquery-m/uri-buildquerystring

It's always a good idea to separate the steps to be able to debug them. You can see that I have Url after in at the end to just evaluate Url and its predecessors. If I know that the Url is correct, I start debuging WebContent and so on.

= let    
    Date = DateTime.ToText((DateTime.LocalNow()), "dd-MM-yyyy hh:mm") ,   
    Date2 = DateTime.ToText((DateTime.LocalNow()), "dd-MM-yyyy hh:mm"),
    Url = "http://a website/data/glovadata/search/csv?" & Uri.BuildQueryString([StartTimeLocal=Date, EndTimeLocal=Date2]),
    WebContent = Web.Contents(Url),
    Kilde = Csv.Document(WebContent,[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Url

[Anonymous]

Thank you @Nolock 

But the question still remains regarding the "StartTimeLocal=Date". I am still unable to put -x days from todays local date and time. Usually you would say - 1 at the end of ".LocalNow()" or am i wrong? 

[Nolock · Accepted solution]

Hi @Anonymous,

you can use the PowerQuery function Date.AddDays. I've modified the code that you can see how to use it - now is the interval from (now - 1 day) till now.

let 
    Now = DateTime.LocalNow(),
    Yesterday = Date.AddDays(Now, -1),
    Date = DateTime.ToText(Yesterday, "dd-MM-yyyy hh:mm") ,   
    Date2 = DateTime.ToText(Now, "dd-MM-yyyy hh:mm"),
    Url = "http://a website/data/glovadata/search/csv?" & Uri.BuildQueryString([StartTimeLocal=Date, EndTimeLocal=Date2]),
    WebContent = Web.Contents(Url),
    Kilde = Csv.Document(WebContent,[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Url

[Anonymous]

@Nolock 

Thank you

it works. However it was showing an URL in black background. So I changed the in URL to in Kilde and then it showed all the tables. it downloaded from that site.