03-16-2017 12:46 PM
I'm a bit of a newbie and have spent the past few hours trying to search for a solution so any help would be appreciated.
I have an excel workbook that has a series of Power Query calls loaded into the data model. The data model is pivoted and the users in my organization access the tables via excel online.
My current refresh involves task scheduler and vba but from what I'm reading a better solution is refresh on a schedule with powerBI. The query accesses folders of CSV files and joins them.
I loaded my report into PowerBI, installed an on-premises Data gateway on my always on machine, and setup the gateway successfully. Wasnt sure what to setup as a data source so i added the file on my Ondrive for business that houses the report, and the folder with the xlsx file/CSV files. Those added successfully. However when I log into PowerBI and hit refresh now, I get an error saying Workbook Refresh Failed and references the first Query.
Not sure what I'm doing wrong or what the next steps are but would appreciate any help I can get.
Office 365 enterprise E3 - PowerBI Pro
03-19-2017 07:41 PM
From your description, it seems that you create a Excel workbook use Power Query connect to local CSV files, then you upload both Excel workbook and CSV files into OneDrive for Business, right?
As the CSV files location changed after moving to OneDrive, make sure the Power Query corresponding changed regarding connection string.
03-19-2017 08:41 PM
I have an xlsx workbook with multiple queries each associated to a different folder of csv files. The files are joined using functions that are invoked as a custom column.
The workbook never leaves onedrive for business but recently I read a thread from 2015 that said powerBI can't refresh queries that are using functions. Not sure if that is still true or if I even read it right but the error message seems like it.
"You can't schedule refresh for this dataset because one or more sources currently don't support refresh."