(URL) => Web.Contents(URL)
Only the following is allowed:
Web.Contents("https://bing.com")
(You can pass in dynamic headers or query string parameters.)
I propose that you enable Power Query inside the Power BI service to retrieve dynamic base URLs. Please add a setting where I can authorize a particular Power Query data source to grab any URL. This scenario already works inside Power BI Desktop or Excel in that Power Query can retrieve dynamic URLs after the user sets the auth for each dynamic base URL.
The current workaround is to pass the URL you want to a hardcoded http://YourSiteHere.com/redirect.aspx?url=http://yourdynamicURL.com
But that it not a great workaround.
Comments
This is a farce - by now there are several blog posts out from respected authors on how to hack around this arbitrary restriction, so it is a pointless annoyance.
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
2018 and no response from PowerBI. I get it is a more advanced feature. But we need it soon. I try making this for SharePoint files (because they don't appear in the files list because of the limit).
Doen't work at all if i need to query same files on different servers in webfarm.Failed
Do not work for Web API (using a API Key).
Exemple M :
Web.Contents(
"https://api.foursquare.com/v2/users/self/checkins",
[Query=[
#"limit"= Number.ToText(pagesize),
#"offset"=Number.ToText((page-1)*pagesize),
//#"oauth_token"="***************",
#"v"="20140701"],
ApiKeyName="oauth_token"
])
),
For Web.Content it looks like there is some support for parameters.
If the source is:
Source = Json.Document(Web.Contents("https://data.gov.uk/api/3/action/package_search?q=blah"))
Just add in a Query option with the parameter, like this:
Term = "blah",
Source = Json.Document(Web.Contents("https://data.gov.uk/api/3/action/package_search?q=default", [Query=[q=Term]]))
In our case our API which I'm trying to hit has params as slugs in the URL itself, which does not seem to be supported yet. e.g. https://our-api.com/{term}/data/stuff
Reference:
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/
Web.Contents (Query option):
https://msdn.microsoft.com/en-us/library/mt260892.aspx
Guys, this is absolute deal breaker for less tech savvy users. Cant understand how such mishaps are even possible. I spent hours and hours on building my report in PBI desktop to learn that it will eventually fail in PBI Online. Terrible.
This seems to be am important limitation ! And also imortant because of the difference in behaviour between desktop and BI service ... in the desktop these dynamic URL parameters work fine and thenw hen publishing the report the refresh fails...as Microsoft is aiming at putting the desktop and the service on the same level this kind of mismatches should be avoided I think...
Hi Greg, Could you please advice me more on how you got the URL to work wit the above workaround, I can't seem to get it working. It refreshes on the Power BI desktop when I use dynalic urls but not in the cloud service
Hi Greg. Could you be more descriptive of the alternate fix you came up woth to refresh dynamic URL. I can't seem to get the above url working
In the meantime, a better warning message that tells you why you can't refresh that Power Query inside the Power BI service would be helpful.
Administrator on 6/17/2021 12:18:45 AM
Power Query supports dynamic URL refreshes with the Web.Contents function - https://docs.microsoft.com/en-us/powerquery-m/web-contents