I am using PowerBI Service to connect to an Excel file on Sharepoint.
I have used an excel file ("Connection.xlsx") in a sharepoint folder with power query to load the data (add to data model) from another file, which works fine. The queried file in Power Query is also on Sharepoint. This works perfect.
I reference connetion.xlsx as part of the Get Data process in Power BI Service, point it to Connection.xlsx on Sharepoint and the file loads perfectly with all rows imported. I can generate reports and dashboards.
Since Connection.xlsx will be referencing a folder structure in the form of "Year-Month" (YYYY-MM) I used a simple M function in Power query to calculate the month.... called "Full_Path"
Full_Path:
() =>
let
xPath = "https://blahblah.sharepoint.com/blahblah/Shared Documents/" & DateTime.ToText(DateTime.LocalNow(),"yyyy-MM") & "/"
in
xPath
My Query then becomes....
let
Source = Excel.Workbook(Web.Contents(Full_Path() & "blah.xlsx"), null, true),
Table_ES_Table = Source{[Item="Table_ES",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table_ES_Table,{{"Product Name", type text}, {"Product Version", type text}})
in
#"Changed Type"
As say, this all works fine. No problem, the "Full_Path" M function works as expected.
However, when I go to shedule a refresh it fails with:
Unable to refresh the model (id=689018) because it references an unsupported data source.
(NOte: the data is loading fine in Power Query and Power BI even with this M Function. Its just the refresh that fails.
If I remove the "Full_Path()" function and hard code the sharepoint path, this refresh starts working again.
Is there any known workaround here for when the the power query is a just a little bit complex that it stops the query from refreshing.
Thanks for the assisance.
Gerard
