The Problem
Recently , We developed a solution for one of our clients which is getting data from multiple Excel files in a folder.
This is a relatively simple task for Power BI get data. Since there is no built-in functionality for this scenario , We used a user defined function to extract the data like described in Ken Puls blog .Everything was working great, And the model refreshed perfectly, Even when more Excel files with the same structure were added to the folder.
But when we uploaded the pbix and tried to define a scheduled refresh using personal gateway we got the not-so-informative message:
"you cannot shcedule refresh this dataset because it gets data from sources that currently dont support refresh"
Now, this could have many reasons, But after doing some digging we found it was related to the Excel files merge.
The Power BI Limitation
It turns out that when the Power BI service tries to recognize the data sources for refresh in the M script (the language behind get data/Power Query ) , A data source function cannot use another dynamic function to determine it's parameters.
So, This will work:
let
Source = Excel.Workbook(File.Contents("D:Some FolderSome Excel File.xlsx")),
But this won't:
(filepath) =>
Let
Source = Excel.Workbook(File.Contents(filepath)),
Now, Once you understand the problem, You can find a workaround.
The Workaround
So, Rewriting Ken's solution, We used the same logic:
Pull the folder information, But then, change the user defined function which gets the file path and return the data in the sheet, to get the actual content , which is already there anyway !
So instead of this :
(filepath) =>
let
Source = Excel.Workbook(File,Contents(filepath)),
Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data]
…….
We do this:
(Content) =>
let
#"Imported Excel" = Excel.Workbook(Content),
Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data]
…….
And then , Add column ? fnGetContents([Content])
instead ? fnGetContents([Folder Path]&[Name]) which will fail in Power BI scheduled refresh
This way – We moved the dynamic parameter to be the content, and not the path to overcome the limitation
More cases
This limitation can be problematic in other cases like when connecting for example to a web reporting API . in many cases , Some of the request logic, Is part of the API call. Like in this case:
Let
options = [Headers =[#"Accept"= "application/xml"]],
result= Xml.Tables(Web.Contents("https://myapi.com/Data/entries?from=20140101&to=20161230/", options)),
In this case, The start and end dates are part of the URL, so I cannot use a function to extract them:
result= Xml.Tables(Web.Contents(https://myapi.com/Data/entries?from=20140101&to= " & DateTime.Date(DateTime.LocalNow()) & "/", options)),
Conclusion
The Power BI scheduled refresh mechanism have a certain limitation that we should be aware of.
Some creative workarounds can be helpful
Does anyone else experienced these issues ? Have some further information about it ?
