Forum Discussion

NicolasMKEO's avatar
NicolasMKEO
New Member
7 years ago

Scheduled Refresh for Web.Content

Dear Community, 

 

I've been trying to publish a dataset on the https://app.powerbi.com service. 

I'm stuck with the refreshing feature though. There is my workflow : 

 

I create a query on Desktop, running API calls : 

 

let      

 url = "https://platform.gaelenlighten.com/api/reports?FormName=Safety%20Incident%20Investigation%20Report&&ReportStatus=All&&&&&&&&&&&IsMandatory=All&&&&&&&&OrderBy=Default&OrderByDesc=Default&ThenBy=Default&ThenByDesc=Default&&&key=apikey",
Options = [Headers=[ #"Authorization" = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",#"Tenant" = "xxxxxxxx.gaelenlighten.com"]],

iterations = 1000, FnGetOnePage =   (url) as record =>    let     Source = Json.Document(Web.Contents(url, Options)),     data = try Source[Reports] otherwise null,     next = try Source[Links]{1}[RequestUrl] otherwise null,     total = try Source[TotalResults] otherwise null,     res = [Data=data, Next=next, Total=total]    in     res, GeneratedList =   List.Generate(    ()=>[i=0, res = FnGetOnePage(url)],    each [res][Total] > 0 and [res][Data]<>null,    each [i=[i]+1, res = FnGetOnePage([res][Next])],    each [res][Data]) in     GeneratedList

This works fine in PBI Desktop. 
When publishing, the refresh does not work on the app.powerbi.com : Web.Content is not supported. 
From there, I use the workaround explained here : https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

 

And modify the code to get : 

let
   

 url = "https://platform.gaelenlighten.com/api/reports?FormName=Safety%20Incident%20Investigation%20Report&&ReportStatus=All&&&&&&&&&&&IsMandatory=All&&&&&&&&OrderBy=Default&OrderByDesc=Default&ThenBy=Default&ThenByDesc=Default&&",

Options = [Headers=[ #"Authorization" = "Basic xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",#"Tenant" = "xxxxxxx.gaelenlighten.com"]],

iterations = 1000,
FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents("https://platform.gaelenlighten.com/api/reports?FormName=Safety%20Incident%20Investigation%20Report&&ReportStatus=All&&&&&&&&&&&IsMandatory=All&&&&&&&&OrderBy=Default&OrderByDesc=Default&ThenBy=Default&ThenByDesc=Default&&", Options)),

    data = try Source[Reports] otherwise null,
    next = try Source[Links]{1}[RequestUrl] otherwise null,
    total = try Source[TotalResults] otherwise null,
    res = [Data=data, Next=next, Total=total]
   in
    res,

GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [res][Total] > 0 and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

The refresh online are not showing error apparently.

Thought, when I access my dataset, i do get :

"Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again."

The credential type is anonymous, which is the one I use in desktop. Not working here though, while perfectly working in desktop. 

 

I've tried different types, but couldn't make this work. 


Any support would be much appreciated !

 

Thank you,

Nicolas

 

 

 

 

 

 

1 Reply

  • v-xicai's avatar
    v-xicai
    Community Support

    Hi NicolasMKEO ,

     

    1.Please try to upgrade the Power BI Desktop to the latest version, then republish again.

     

    2.Your data source credential may expire, so try to update the credential and republish the report. In Power BI, go to refresh settings for the dataset. In Manage Data Sources, select Edit credentials to update the credentials for the data source.

     

    3.If your data source contains local data source, then you may need to install and configure the latest data gateway. See more :On-premises data gateway .

     

    4.Mashups between a cloud source and an on-premises source, in a single query, fail to refresh in the gateway (personal mode) if one of the sources is using OAuth for authentication. An example of this issue is a mashup between CRM Online and a local SQL Server instance. The mashup fails because CRM Online requires OAuth. To work around the problem, have a separate query for the cloud source and the on-premises source. Then, use a merge or append query to combine them.

     

    If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support , Scroll down and click "CREATE SUPPORT TICKET", to get further help.

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.