cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Alisina,

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

View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Hi @Alisina,

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

 

Highlighted

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? 

 

 

Highlighted

Hi @Alisina,

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

View solution in original post

Highlighted

@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.

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors