Dynamic connection to Web URL with possibility of auto update in Powerbi.com

[Anonymous]

Hi

I have a code to call for data from a dynamic URL.  After many mistakes I can finally make the url dynamic (change date within 24hrs at all time of update), The project can be uploaded to powerbi.com for auto update. However when trying to define "data source credentials"

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("link",
        [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

[v-frfei-msft]

Hi @Anonymous ,

Did you set the Privacy level to none ? And which verification method did you use here? Please keep the same in Power bi desktop and Power bi SERVICE.

[Anonymous]

@v-frfei-msft 

I have changed some in the code again, using Query instead of uri.builquerystring() to encode datetime data to URL.

However, the code works fine in Dekstop. But has problem in powerbi.com where it keeps "spinning (loading)" when I choose the legitimation.

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
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("..............." ,[Query=[StartTimeLocal=Del1,EndTimeLocal=Del2]]),[Delimiter=";", Columns=23, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
         Source

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

Thanks 

[Anonymous]

Hello

Yes I put both the same as desktop to powerbi.com. It keeps spinning as shown. I waited for 20 min in powerbi.com with it spinning..But nothing happened so i cancelled it, as I think there is a problem..Did you make it work?