Need Masters in Web.contents and Query to read dynamic URL data

[Anonymous]

I have during some time now tried to read a dynamic web data in PowerBi. Using Query in web.contents to add the dynamic part of the url to the main url to get the data. By not using Query and just using "&Terms" is also possible, but the option to auto update the data in powerbi.com is no longer available. So, Query must be used to attach the second part of the url. But I have problem in powerbi.com where it keeps "spinning (loading)" when I choose the legitimation. see picture:

I have tried Anonymous, Basic and Windowswithoutimpersonation, each with all 4 combinations: None, private, organizational and public.. All of them keeps spinning and nothing happens.

However I get an error if I dont choose the Niveau for protection, meaning either none, private, organizational or public, the following error appears when pressing ok:

Cannot update the following data source credentials: The credentials specified for the Web source are invalid. (Source at Hide details
Activity ID: 31415b80-0406-4665-bc7a-54b688fe7cf5
Request ID: b89f449e-dd8f-e34c-02ef-94c2c9658c90
Status Code: 400
Time: Thu Apr 11 2019 11:58:16 GMT + 0200 (Central European Summer Time)
Version: 13.0.9059.132
Cluster URI: https://wabi-north-europe-redirect.analysis.windows.net

Additionally, I have changed data source settings to match the powerbi.com and desktop data source setting and legitimation information.

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","Dato2"},
    {{Date,Date2}}),
    Del1 = Terms{0}[Dato1],
    Del2 = Terms{0}[Dato2],

            Source = 
         Csv.Document(Web.Contents("link" ,[Query=[StartTimeLocal=Del1,EndTimeLocal=Del2]]),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
         Source

I suggest that  you publish the report and see if you can make an auto update schedule.

I hope someone has a clue about this, as I have now used weeks on this code.

Thanks 

[TrooP]

Hi i have a similar issue where data update works fine in desktop but not at all in powerbi.com..

let
    Date = DateTime.ToText((DateTime.LocalNow()), "yyyy-MM-dd"),
    DateOld = DateTime.ToText((DateTime.LocalNow()-#duration(7,0,0,0)), "yyyy-MM-dd"),
    Källa = Json.Document(
        Web.Contents(
         "https://lorasensorsapi.azurewebsites.net/api/devicedata/xxxx?fromDate=2019-04-10&toDate=2019-04-11&expandAdditionalInfo=false&grainSize=hour",
           [
            Query=
                [
                     fromDate=DateOld,
                     toDate=Date,
                     expandAdditionalInfo="false",
                     grainSize="hour"
                ],      
            Headers=
                [
                    Authorization="Basic xxxx"
                ]
            ]
        )
    ),

the problem i have here is that i need the whole string to be able to authorize and powerbi.com removes all the query parameters for login..

and i am using anonymous login..

(i have also tried the uri without the "old" querys but with the same results... )

i need to refresh this data daily for a published report but am not managing to do this right now...

I can get the data refresh to work if i change the web.contents to:

Json.Document(Web.Contents("https://lorasensorsapi.azurewebsites.net/api/devicedata/xxxx?fromDate=" & DateOld &"&toDate=" & Date &"&expandAdditionalInfo=false&grainSize=hour", [Headers=[Authorization="Basic xxxx"]])),

but then autorefresh is not working because powerbi.com cannot handle variables in the uri...