cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JmvSteenbergen Frequent Visitor
Frequent Visitor

Refresh works in Power BI desktop, but not in the service

Hi all,

I am facing an issue with loading data from a .csv file hosted on the web. Everything works and refreshes successful in Power BI desktop, but after I publish the report to the service it does not allow for refresh.

 

  1. In the first query I connect to Azure SQL to get a URL and then drill down into the result so I have a text field that can be used as input for the second query.

  2. In the second query I use the URL from the first query in a Web.Contents function and add some details to get the list I need:

Source = Xml.Tables(Web.Contents(bloburi & "&restype=container&comp=list")),

Then I do some transformations in Power Query to get the exact URL file name linking to a .csv file from the web. So far so good, no issues appeared.

  1. Now that I got the exact link to the .csv file I want to extract the data from this file, by again using the Web.Contents function with as input the FileURL:

BinaryURLContents = Table.AddColumn(#"Added Custom1", "BinaryFiles", each Web.Contents([FileURL]))

This results in a Binary file that I can expand and successfully extract the data from.

After troubleshooting and breaking down queries, I found from what step the issue appears / starts: 3 converting the link to a binary csv flat file.

  1. In step 4 I have parsed the binary flat file using a function and successfully extracted the data, I won’t go into too much detail here, as the issue is caused by step 3.

 

I have played around with changing the FileURL used as input for the Web.Contents, because when Power BI cannot successfully test the URL you won’t be able to refresh. I have successfully done that before with help of Chris Webbs blog: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power.... Unfortunately, in this case it does not cause the issue, because both the FileURL & URL used in the Web.Contents function are correct and can be used & accessed through the browser.

I have also tried to provide the FileURL through a parameter or another query, but if this references and makes use of the first query step then I face the same issue. Strange enough: if I directly (hardcoded) provide the FileURL in the Web.Contents function it does allow for refresh.

I think the issue is that I try to use a query with a web.contents function as input for another web.contents query and that Power BI does not know it has to execute query 1 before query 3 otherwise query 3 does not have the input it requires, but I am not sure and don’t know how to resolve it.

Error message in the Power BI service:

You can't schedule refresh for this dataset because one or more sources currently don't support refresh.

Data source error: Unable to refresh the model (id=1539922) because it references an unsupported data source.

 

I don’t see how the data source would be unsupported as I connect to web sources (Web.Content).

 

Any help, idea’s or pointers are much appreciated!

 

 Jeroen

4 REPLIES 4
Community Support Team
Community Support Team

Re: Refresh works in Power BI desktop, but not in the service

Hi @JmvSteenbergen,

From your description, could you have configured gateway for your web source in Power BI service?

You could refer to this link to configure your gateway.

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem#how-the-gateway-works

Also, you could refer to this link that may have the same problem with you:

https://community.powerbi.com/t5/Service/Trouble-avoiding-gateway-installations-for-scheduled-refres...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JmvSteenbergen Frequent Visitor
Frequent Visitor

Re: Refresh works in Power BI desktop, but not in the service

Hi Daniel,

Thank you for your response.

I do not have the option to use a Gateway, all it says is:
"You can't schedule refresh for this dataset because one or more sources currently don't support refresh."

I have added the exact web address as data source successful to the Gateway in an attempt to see if the Gateway option shows up, unfortunately it does not.

I think the main difference is that I do not use the Web.Page function in combination with Web.Contents, I use the FileURL column from the previous query step and extract the csv data from those links using the Web.Contents function:

BinaryURLContents = Table.AddColumn(#"Added Custom1", "BinaryFiles", each Web.Contents([FileURL]))

I have tried to change the Web.Contents function to the Web.Page function, but this only gives me the URL, not the actual data stored on that url.

JmvSteenbergen Frequent Visitor
Frequent Visitor

Re: Refresh works in Power BI desktop, but not in the service

Any other ideas?

Ajay123 Regular Visitor
Regular Visitor

Re: Refresh works in Power BI desktop, but not in the service

Hi @JmvSteenbergen,

 

How did you manage to solve this issue, I am facing the same issue with a similar business case..

 

Thanks,

Ajay