cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alisina Regular Visitor
Regular Visitor

URL based date dependent data request from server - M-script/Query

Does anyone know how to fix this code so it receives data.

 

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"),

Terms = #table({"Dato1","Dato2"},
{{(Uri.BuildQueryString(Date)), (Uri.BuildQueryString(Date2))}}),
SearchSuccessful = (Dato1,Dato2) =>
    let
        Source = 
         Csv.Document(
           Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?", 
             [Query=[StartTimeLocal=Dato1]&[EndTimeLocal=Dato2]]
            ),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]
           ),
        Success = Source[success]
    in
	Success,

     Output = Table.AddColumn(
       Terms, 
       "Search Successful", 
       each SearchSuccessful([Dato1],[Dato2])
      )
    
in
    Output

At the moment I get 3 columns with (Error) inside.

The Idea is to send an URL: "http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=08-04-2019%2002%3A12&EndTimeLocal=09-04..."

 

If the url link generated is not the same as above (with changeable date (last 24 hr data) ), then no data will be received. As the the database requires date to output data. 

The reason I use the shows method is so the data is updateable in powerbi.com

see link for reason: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

20 REPLIES 20
Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

I would place bets on @ImkeF  


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

What does the error-message say?

 

Also, please note this passage from the blogpost you've mentioned: " This technique will only work if the url passed to the first parameter of Web.Contents() is valid in itself, is accessible and does not return an error.  "

Your URL doesn't look like that.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

The Error is shown as follows:

image.png

 

Pressing the Error in Search Successful shows:

Expression.Error: We cannot convert the value "08-04-2019 08:21" to the Record type.
Details:
     Value = 08-04-2019 08:21
     Type = type

Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

I have now reduced the code to a half working state:

 

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

        Source = 
         Csv.Document(
           Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?",
             [Query=Terms]
            ),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]
           )

in
    Terms

By running the "in Terms" it is clear that the date dependent urlencoded code generated in Terms is correct and is as it should be.. However, I do not know why it is not merging with the rest of the URL "http://Yoursite.com/data/glovadata/search/csv?", even though the [Query=Terms] is given.

The following Error is displayed when "in Source" is used in the end:

DataSource.Error: Web.Contents failed to retrieve content from 'http://Yoursite.com/data/glovadata/search/csv' (400): Bad Request
Details:
     DataSource Kind = Web
     DataSourcePath = http: //Yoursite.com/data/glovadata/search/csv
     Url = http: //Yoursite.com/data/glovadata/search/csv

Alisina Regular Visitor
Regular Visitor

URL and Query combination to complete a URL and receive data

I am trying to manage a code that I have been working on for some days now. 

The code runs fine and calls data if I get it out of table. I can update in PowerBi Desktop. However when uploaded to Powerbi.com, it does not update. 

I found a site: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

Chris the blog above showed another way, where you have to wrap part of the code in Table in order for it to work. However after many tries, I came up with this code. However Query is not attached to the URL to finish the URL which looks like: "http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=31-03-2019%2002%3A12&EndTimeLocal=01-04..."

 

Where the StarteTimeLocal and EndTimeLocal receives new date and time to get newest datas.

This method is supposed to also update in powerbi.com. Yet my code has a problem which it doesnt attach the Query to the URL before it calls the site for data. any suggestion?

 

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

        Source = 
         Csv.Document(
           Web.Contents(
             "http://Yoursite.com/data/glovadata/search/csv?",
             [Query=Terms]
            ),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None]
           )

in
    Terms

By running the "in Terms" it is clear that the date dependent urlencoded code generated in Terms is correct and is as it should be.. However, I do not know why it is not merging with the rest of the URL "http://Yoursite.com/data/glovadata/search/csv?", even though the [Query=Terms] is given.

The following Error is displayed when "in Source" is used in the end:

DataSource.Error: Web.Contents failed to retrieve content from 'http://Yoursite.com/data/glovadata/search/csv' (400): Bad Request
Details:
     DataSource Kind = Web
     DataSourcePath = http: //Yoursite.com/data/glovadata/search/csv
     Url = http: //Yoursite.com/data/glovadata/search/csv

Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

You have to adjust your main-URL. See the blogpost you've mentioned:

 

image.png

The query will return values for oranges, although the main URL contains query-parameters for apples. 

So you just have to add a hardcoded query-string to your main URL. That will then be overwritten by the values from the Query-record.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

Hi,

I have done as you and the blog mentioned. However, what is written in that blog regarding the first URL will be ignored and the Query read, is wrong. I get data from the URL and not from the Query.

This is a reduced and edited code version which works, but only using the first parameter of the URL already put as suggested. It does not read the Query somehow.

 

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

        Source = 
         Csv.Document(Web.Contents("http://Yoursite.com/data/glovadata/search/csv?StartTimeLocal=31-03-2019%2002%3A12&EndTimeLocal=01-04-2019%2002%3A12",
        [Query=Terms]),
        [Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])

in
    Source

@ImkeF 

Super User
Super User

Re: URL based date dependent data request from server - M-script/Query

Thats because the Terms must be a record and not a table.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Alisina Regular Visitor
Regular Visitor

Re: URL based date dependent data request from server - M-script/Query

@ImkeF 

 

I have now remade parts of the code so it accepts the URL and can be uploaded to powerbi.com for auto update. However when trying to define "data source credentials" section and defining the Web access method, see picture:

2.JPG

If i assign the safety niveau to anything it comes with loading screen like: 

3.JPG

and keeps showing loading dots and cant come away from this screen.

 

The Code updates fine in Powerbi desktop.

This code was partially built using:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

 

Please try to read the code in your Powerbi.com and see if you can make it update? Since I seem to not be able to. thanks

The code:

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"),

Terms = #table({"Dato1"},
{{(Uri.BuildQueryString([StartTimeLocal=Date,EndTimeLocal=Date2]))}}),

SearchSuccessful = (Terms) =>
let
        Source = 
         Csv.Document(Web.Contents("http://onboard.dsb.dk/data/glovadata",
        [RelativePath="search/csv?"&Terms]),
        [Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
  
in
    Source,
     Output = Table.AddColumn(
       Terms, 
       "Search Successful", 
       each SearchSuccessful([Dato1])
      )
in
    Output