Scheduled Refresh Custom Function

Hi Anonymous,

Alright good to know I can translate each query to a parameterized query. However, I did this test for one of the queries and when I try to schedule a refresh now I get:

Failed to update data source credentials: The credentials provided for the Web source are invalid. (Source at <URL>.)Hide details

The data is loading perfectly within Power BI never have any problems, but when I try to schedule a refresh it fails. Any tips to this?

Anonymous the suggestion you make to use it in a query parameter, how do you that?

Currently I use an API query to receive IDs that need to be used in the second query

So for example: 

Source = Json.Document(Web.Contents("URL" & "ParamX=" & param1, [Headers=[Authorization="Bearer " & #"Token"]]))

That results in IDs that need to be used in the second query. Basicly for each ID from the above code, do the code below:

Source = Json.Document(Web.Contents("URL2" & "&ParamY=" & param2 , [Headers=[Authorization="Bearer " & #"Token"]]))

Can you tell me how to do that using query parameters?

Because the following code still fails in the web refresh as it cannot register my credientials:

 Source = Json.Document(Web.Contents("URL" & "url_part_1", [Headers=[Authorization="stringtoken"]])),
    

It says it cannot be validated in the online environment, while locally it works perfectly fine.

The other question also remains on why the scheduled refresh is not working!

Hi cwebb, Chris,

Thank you for that post and elaboration, that is amazing and I will look in this and this seems to be the solution! Do you know if this also works when you need to pass Authorization towards the URL in order to validate the URL?

Kind regards,

Dennis

Hi, cwebb great post, still I have a small issue, my code has two parameters, one part of the URL, the other is a query, thanks to your post I solved the second, still can´t find the right code for the first one.

Parameters are player and season.

Original code:

(Season,Player)=>

let
    Source = Web.Page(Web.Contents("http://www.espnfc.com/player/" & Player & "?season="&Season)),
    Data3 = Source{3}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Team", type text}, {"Oppo", type text}, {"Date", type date}, {"Comp", type text}, {"Res", type text}, {"Appear", type text}, {"g", Int64.Type}, {"a", Int64.Type}, {"sh", Int64.Type}, {"sg", Int64.Type}, {"fc", Int64.Type}, {"fs", Int64.Type}, {"yc", Int64.Type}, {"rc", Int64.Type}})
in
    #"Changed Type"

New code:

"Season" parameter is working, "Player" is the one I'm having the problem with, can't find the right code.

(Season,Player)=>

let
    Source = Web.Page(Web.Contents("http://www.espnfc.com/player/" & Player,
        [Query=[season=Season]]
        )),
    Data3 = Source{3}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data3,{{"Team", type text}, {"Oppo", type text}, {"Date", type date}, {"Comp", type text}, {"Res", type text}, {"Appear", type text}, {"g", Int64.Type}, {"a", Int64.Type}, {"sh", Int64.Type}, {"sg", Int64.Type}, {"fc", Int64.Type}, {"fs", Int64.Type}, {"yc", Int64.Type}, {"rc", Int64.Type}})
in
    #"Changed Type"

Your support is appreciated.

Regards,

Oscar