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:

Answer 1

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"]]))