Forum Discussion

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

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

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

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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

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

         

        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