Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Automatic refresh of a power bi report with a dynamic sharepoint list data source

Hello There !


Below is the simplified version of a concept that I'm using in a new Power BI report. This concept seems to prevent me from setting up the automatic refresh of my final report.

1. I have a Query Named "Document" that takes a Sharepoint list as a source and generates the following table.


Sans titre1.png

When I publish to Power BI Online I can configure the data source on the Gateway with no problem whatsoever and the refresh is properly done.


2. Now, since I have an indefinite number of URLs (under the same Sharepoint root) to open and transform using the same steps as in the "Documents" query, I tried to generalize this in a function that takes an URL as a parameter.
I therefore encapsulated the code of the "Documents" query in the "fct_Document" function (the ame is to invoke it later on in a calculaed column)

sans titre2.jpg


When I invoke my function with the same URL used in the "Documents" query, I get the same table as I do with the "Document" query (see first snapshot). Which is good.


sans titre3.jpg


My problem is that now that I have added the "fct_Document" function, Power BI considers that this is a query that uses a dynamic data source and therefore my dataset cannot be set up to be refreshed automaticaly on Power BI online.

In the below screenshot, the message refers to the "fct_Document" by "Query1" which was its orginal name when I first created it.

sans titre4.jpgsans titre5.jpg

Also, when I go to data source settings, I have this warning:


From my understanding the problem comes from this part of the code, where the url argument is a variable


I have seen in the web a fix for a similar problem only it was about accessing an online file through the Web.Contents(URL) function.  Changing this first expretion to something like below fixed the problem:  Web.Contents("https://sharepointroot/", [RelativePath=Rest_of_url]) 


In my case I am working with SharePoint.Tables(...,...) which doesn't have the equivalent of the RelativPath argument.

This is what Microsoft says about Dynamic data sources 


Did anybody have a smilar problem and can help me to fix mine ?


Thanks a lot for your answers




Memorable Member
Memorable Member

You can add sharepoint to a data gateway, then you have the option to "Skip connection test"


I would try this first and let me know if it helps or not

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!
Stay up to date on  
Read my blogs on  


Thanks a lot for taking the time to answer.

I am afraid that there is no use in adding a new sharepoint source in the Gateway since, there is no way I can selected as a source for my data set.

As you can see below the "Gateway connection" and "Data source credentials" sections are missing the data source setting options.


Sans titre.png

This is what Microsoft says about Dynamic data sources 


Best regards,


Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors