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

[Anonymous]

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.

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)

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.

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.

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

Sarah

[SteveCampbell]

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

[Anonymous]

Hi,

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.

This is what Microsoft says about Dynamic data sources 

Best regards,

Sarah