Scheduled Refresh Custom Function

[Soldexio]

Hi everyone,

I'm trying to generate a scheduled refresh for my data sources, however every data source uses our own API endpoints. These are accessible outside the organization, however require certain authentication and parameters. Therefore, I have defined a custom function as follows:

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

However, when I try to schedule a refresh like this, it says the data source is unknown and cannot be scheduled as a refresh. Is there any way to get this working or specify this function in a different way to have it working?

The parameters come from a different custom function and those cannot be specified as a parameter or static variable. Sometimes 700 times param1 & param 2 will go through.

Please let me know as currently manual refreshes are a pain!

[cwebb · Accepted solution]

It looks like you may be running into the issue I describe here:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

Can you try rewriting so that Web.Contents uses the Query option for your parameters?

Chris

[cwebb · Accepted solution]

It looks like you may be running into the issue I describe here:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

Can you try rewriting so that Web.Contents uses the Query option for your parameters?

Chris

[Oscar_Mtz_V]

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

[Soldexio]

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

[cwebb · Accepted solution]

Ah, I didn't see that you are doing authentication using a header - no, I don't think it will work (see the comment by danzrust on the blog post). Sorry!

Chris

[Soldexio]

Unfornate, then I will have to wait until Custom Functions that load in Power BI desktop are supported. Hopefully it will come soon, because this would be a major reason to switch to another BI tool 🙂 cheers!

[v-shex-msft]

Hi @Soldexio,

AFAIK, current power bi service still not support custom function, please convert your query to parameterized query instead custom function.

Remove top row and define param1, param2 as query parameters with accurate parameter type:

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

BTW, current query parameters with 'any' type not allow to changed on power bi service side.

Regards,

Xiaoxin Sheng

[Soldexio]

@v-shex-msft 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!

[v-shex-msft]

HI @Soldexio,

If your query need to invoke multiple times based on result data, I think this scenario not suitable to transform to parameterized query.

It means you need to manually write custom steps to invoke api url with different result data.

Regards,

Xiaoxin Sheng

[Soldexio]

Hi @v-shex-msft,

Could you elaborate what you mean with manually write custom steps? I do want to be able to schedule a refresh and the way I currently did this, does not support scheduled refresh.

Also, do you know what the plans are to schedule refresh on custom functions? Because atleast for my usage of Power BI (and that of the company) these are mandatory to work really. Manual refreshes cannot be done for multiple dashboard that require daily updates!

Kind regards!

[Soldexio]

Hi @v-shex-msft,

Any update on this? Looking to get this to production asap!

Kind regards,

Soldexio

[v-shex-msft]

Hi @Soldexio,

Your scenario is similar as below, it need to analytics response data to invoke them to get next data.

How to perform loop in M Query and collate result of the same

It is a loop operation which only suitable with custom functions so you can't use it on power bi service side.

>>Could you elaborate what you mean with manually write custom steps?

If your query has fixed request frequency, you can manually write custom steps to invoke api and analytics response data.

If frequency are dynamics and you can't confirm how many times it invoke, my suggestion not works for this scenario, you had to use custom function to achieve these operations and can't works on service side.

Regards,
Xiaoxin Sheng

[Soldexio]

Hi @v-shex-msft,

Cheers for the elaboration, I have these working in Power BI desktop, however they do not work in services and scheduled refreshes. Therefore, this cannot be used in our case but I believe that concludes that this is not possible in Power BI sadly.

Thanks for the replies.

[Soldexio]

Hi @v-shex-msft,

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

Activity ID:	b46998c5-dd70-47c2-ac52-2c7095e5dbec
Request ID:	4d4f0423-8fbe-3ad3-c487-4914e391ca35
Status code:	400
Time:	Tue Aug 21 2018 08:51:03 GMT+0200 (Central European Summer Time)
Version:	13.0.6358.131
Cluster URI:	https://wabi-europe-north-b-redirect.analysis.windows.net

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?

[Soldexio]

Hi @v-shex-msft,

I did some testing with webhook tester. The request that the Power BI service is sending, is not sending the authorization token properly. 

host	webhook.site
user-agent	Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
authorization	Bearer
content-length	(empty)
content-type	(empty)

Any idea to why this is and how to fix this? Because this is the reason every request on my side is failing when I translate it to parameterized query. The request within Power BI is as follows:

Headers

connection	Keep-Alive
host	webhook.site
accept-encoding	gzip, deflate
user-agent	Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
accept	*/*
authorization	Bearer 12345
content-length	(empty)
content-type	(empty)

Any help here would be welcome!