Forum Discussion

agkg's avatar
agkg
Frequent Visitor
3 years ago
Solved

Alternatives to dynamic data source

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 i...
Need Help
  • agkg's avatar
    agkg
    3 years ago

    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's avatar
v-jingzhang
Community Support
3 years ago

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.