Web.Contents with Query (Circumventing dynamic dat...

SajjadMousavi · ‎06-18-2022

Hi.

I'm trying to circumvent Power BI service limitation in using dynamic data sources (below error when opening a data source settings)

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

Data source for Query1

I have Googled a lot and found this solution (M code):

Excel.Workbook(Web.Contents("https://onedrive.live.com",[RelativePath = "/download", Query = [resid="someresourceid",authkey="someauthkey",em="2",app="Excel"]]), null, true)

There is a myth that says the above should work and I should be able to refresh this report in service, but it doesn't! The above error persists despite replacing all web data sources in the report (all being OneDrive). In addition, I was unable to find that strange "Data source for Query1" in my data sources! (Note: this is a report which is created by someone else and I have given the responsibility to improve it. I don't have access to the person who had made this mess.).

lbendlin · ‎06-20-2022

Most likely your issue lies elsewhere, in another dependency further down in your transforms. The recommendation to keep everything in one partition still holds true.

Please provide more details.

SajjadMousavi · ‎06-21-2022

When I refresh my data using above code in Power BI Desktop, it refreshes without any problem. So there cannot be any problem with transformations. Please note that Power BI service "detects" this source as dynamic and doesn't allow me to start a refresh at all.

lbendlin · ‎06-21-2022

"When I refresh my data using above code in Power BI Desktop, it refreshes without any problem. So there cannot be any problem with transformations"

That is an incorrect assumption. The query security checks on the service are different than the ones on the desktop. Read about formula firewalls.

SajjadMousavi · ‎06-21-2022

I read this article on Microsoft docs about fomula firewalls, but couldn't figure out its relationship with my case. I don't receive any error(s) regarding firewall (even on service). Can you please explain a little more? I'm really stuck and need to fix this issue.