How to refresh report that uses custom functions on Power BI Service?

Question

I know this issue has been posted many times before, but I was not able to get a solid/working solution.

I am trying to figure out a workaround for refreshing dataset with Custom functions over Power BI service. My query looks like the following:

let
    Clients = Json.Document(Web.Contents("https://api.createsend.com/api/v3.2/clients.json")),
    #"Converted to Table" = Table.FromList(Clients, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ClientID", "Name"}, {"ClientID", "Name"}),

    GetDetails = (ClientID as text) => 
    let 
        Source = Json.Document(Web.Contents("https://api.createsend.com/api/v3.2/clients/" & ClientID & "/campaigns.json"))
    in 
        Source,
    Output = Table.AddColumn(#"Expanded Column1", "Details", each GetDetails([ClientID])),
    #"Expanded Details" = Table.ExpandListColumn(Output, "Details"),
    #"Expanded Details1" = Table.ExpandRecordColumn(#"Expanded Details", "Details", {"Name", "FromName", "FromEmail", "ReplyTo", "SentDate", "TotalRecipients", "CampaignID", "Subject", "WebVersionURL", "WebVersionTextURL"}, {"Details.Name", "Details.FromName", "Details.FromEmail", "Details.ReplyTo", "Details.SentDate", "Details.TotalRecipients", "Details.CampaignID", "Details.Subject", "Details.WebVersionURL", "Details.WebVersionTextURL"})
in 
    #"Expanded Details1"

This works fine on Power BI desktop. However, I am getting following error on Power BI service:

Is your data gateway configured to allow you to connect to api.createsend.com? — Alexis Olson, Dec 01 '18 at 17:54
I reckon it's an authentication issue. You need some sort of authentication on the server. I didn't think the data gateway will serve a json from a web server(?) (re @AlexisOlson). You need to set up authentication, I generally end up using tokens, talk to your server admin on how best to do it. — MikeAinOz, Dec 02 '18 at 23:28
@AlexisOlson I am using API key authorization for connecting. Moreover, the data source is not on premise, so I don't think I really need a gateway. — Pratik Bhavsar, Dec 03 '18 at 04:26
@AlexisOlson I am using API key authorization and the report works fine on Power BI Desktop. However, the Power BI services throws error while refreshing saying the datasource is unsupported. I believe the problem is related to the custom function I used in the query. — Pratik Bhavsar, Dec 03 '18 at 04:29

score 6 · Accepted Answer · answered Dec 06 '18 at 04:36

6

I managed to make it work. With some research, we figured out that the actual problem was not the function but the dynamic API path itself. Power BI verifies the static data source path before refreshing the report on Service.

I solved the problem by replacing :

Source = Json.Document(Web.Contents("https://api.createsend.com/api/v3.2/clients/" & ClientID & "/campaigns.json"))

with this:

Json.Document(Web.Contents("https://api.createsend.com/", [RelativePath="api/v3.2/clients/" & ClientID & "/campaigns.json", Headers=[#"Authorization"="My API Authorization token"]]))

answered Dec 06 '18 at 04:36

Pratik Bhavsar

808
8
32

Hi Pratik, I am using JIRA on premises and it wont have any token. Can you please let me know how can I get this if thats the case? – reddy Apr 14 '20 at 13:37
@reddy You simply need to remove the Headers=[#"Authorization"="My API Authorization token"] – Pratik Bhavsar Apr 15 '20 at 11:04

How to refresh report that uses custom functions on Power BI Service?

1 Answers1