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.
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.