Dynamic data source error

[Cybacor]

Hi

We have built a report, which refreshes fine in the desktop tool, however when trying to setup a scheduled refresh in the browser, this error appears;

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed

We are taking a site URL (well quite a few), and adding an additional portion  to the URL;

let LoadProjectStatusReports = (site as text) =>
let
Source = OData.Feed(site&"/_api/web/lists/GetByTitle('Project Status Reports')/Items()?$Select=Title")
in
Source
in
LoadProjectStatusReports

Is there a way to adapt this, so we don't get the error? We pull a list of URLs ok, then want to add on the bit in quotes.

[Halkjaer]

The OData.Feed() functions allows you to add headers and parameters dynamically outside of modifying the URL.

• Edit: after rechecking the documentation and your description I have come to realise that OData.Feed() doesnt have the RelativePath modifier, so it won't solve your use-case because you need to modify the base URL.

The basic syntax is:

OData.Feed(serviceUri as text, optional headers as nullable record, optional options as any)

What you want to do is to add your dynamic value parameters as options or headers instead of injecting them into the serviceUri directly.

Aside from being a different function with slightly different syntax—the solution remains somewhat the same to what Laura describes in her blog post:

https://hatfullofdata.blog/power-query-dynamic-data-source-and-web-contents/

Also, check out the MS documentation that explains the function and its options in details:

https://learn.microsoft.com/en-us/powerquery-m/odata-feed

[v-jingzhang]

Hi @Cybacor 

Is it possible to query data from the url by using Web.Contents() instead of OData.Feed? If it is, you can try passing the site url to the url paramter, and passing /_api/web/lists/GetByTitle('Project Status Reports')/Items() to the RelativePath option and Select=Title to the Query option.

Source = Web.Contents(
 site,
 [
  RelativePath="_api/web/lists/GetByTitle('Project Status Reports')/Items()",
  Query=[$Select="Title"]
 ]
)

The following blogs talk about the relevant solution about this topic:

Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

Power Query – Dynamic Data Source and Web.Contents() 

However, OData.Feed doesn't have the RelativePath option so it cannot apply above solution to OData.Feed. You can refer to the 7th message in this thread: https://community.powerbi.com/t5/Desktop/OData-Feed-command-with-query-and-options-no-working/m-p/11... 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Cybacor]

Hi, can anyone help?