Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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-2019%2002%3A12"

 

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-bi/

Dataflow
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks

20 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      The Error is shown as follows:

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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