Alternatives to dynamic data source

[agkg]

I've made a Power BI report for my company, which shows information about our various ongoing projects. There's a database containing most of the data I need for all the projects. One of the fields in the data is the URL for project's SharePoint site, and on each SharePoint site is a SharePoint list that I also have to get data from. Each project's SharePoint list is formatted in exactly the same way, so I'm able to fetch them all and compile them into one table.

All that works great, in Power BI desktop. My problem comes after publishing the report: The process is a dynamic data source, and therefore can't be automatically refreshed in Power BI Service. I have a few kludgy workarounds in mind, but I'd like your advice. What should I change in my process to be able to automatically refresh my Power BI dataset?

[agkg · Accepted solution]

I was able to solve the problem. Here's the blog post that pointed me in the right direction: https://hoosierbi.com/2022/11/12/updated-get-sharepoint-list-data-fast/

Since all the project SharePoint sites have the same root site, I was actually able to use Web.Contents() and RelativePath to fetch the List contents. The one downside is that it doesn't come with the company's Active Directory data so that had to be retrieved separately, but that wasn't a big deal.

For future reference, here's the relevant part of the query:

= Table.AddColumn(#"Previous Step", "New Column Name", each Json.Document(Web.Contents("https://company.sharepoint.com/sites/projects/", [RelativePath = [TrimmedProjectUrl] & "/_api/web/lists/GetByTitle('ListName')/items", Headers = [Accept = "application/json"]])))

[v-jingzhang]

Hi @agkg 

Currently there is not a good solution to deal with the dynamic data source problem for SharePoint lists. Reference: Refresh and dynamic data sources 

In most cases, Power BI datasets that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed. 

However, when connecting to SharePoint Lists, it uses SharePoint.Tables function but this function doesn't support RelativePath and Query options. In addition, Web.Contents doesn't support to connect to a SharePoint list based on my test. 

You may need to connect to all SharePoint lists separately with their specific URLs then combine them into one query. 

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