Is it possible to refresh an Excel workbook - via the Power BI Service - that has been uploaded (not imported) to an app workspace? The data model for my Excel workbook is the Power BI data model and I'm connecting using the 'Analyse in Excel' option
If we can't refresh Excel workbooks that have been uploaded to an app workspace then what is this feature really for?
1) In a worksapce navigate to the datasets
2) Select 'analyse in Excel' option for a dataset
2) Excel will open and using the pivot table tools make a pivot table from the datset data
3) Test the report refreshes (Data > Refresh ALL) and then save the workbook to file system
3) From the workspace select Get Data > Local file then select the workbook form your filesystem
4) Select the Upload to Power BI option, not the import option (that won't work either but that is by design)
5) Open the workbook from the workbook section of the workspace and try and refresh
@richardlhughes does the Power BI publisher publish workbooks to the Power BI service? also if I publish content from my Excel workbook to Power BI does the Power BI service manage the refresh?
@edwardrmiles there is also a very good solution that you may use, and that I implemented for a Client.
Host your Excel File(s) on SharePoint and establish a connexion from Power BI to SharePoint.
Then, you may schedule data refresh at anytime from Power BI Service, up to 10 times a day.
You mayu also refresh data on demand.
If you are interested, it's quite an easy process, I can explain you how it works. It requires less than 1 minute to refresh 23 Excel files.
@edwardrmiles the add-in allows you to connect to datasets in your Power BI workspace. Also, you can publish the Excel files to the workspace and manage the data refeshes in the Power BI service.
@richardlhughes I know I can use the Power BI publisher to connect to Excel to Power BI Models and publish parts of an Excel workbook to Power BI but I didn't know / cannot see how I can use the Power BI publisher to publish an entire workbook to Power BI and / or manage data refresh
Could you explain how?
@augustindelaf yes, I would be interested - my client doesn't use SharePoint, however if I could demo it on SharePoint online, then that might be a solution. Thanks
@edwardrmiles Just establish a connexion between Power BI Desktop and a "SharePoint Folder" (Get Data>Sharepoint Folder) and enter the URL for the Sharepoint folder root (see screenshot). Your Microsoft Office credentials will be required, enter them.
After that, you will be able to see all the files you have on SharePoint, regardless where they are in the folder. Just select te one you want and you will start seeing them in Power BI Desktop.
Is it clear for you ?
I can show you the next steps if you are already familiar with the beginning.
We have this feature disabled in my environement. However, you should be able to publish content to Power BI using the built-in Publish button in Excel. https://docs.microsoft.com/en-us/power-bi/service-publish-from-excel
@richardlhughes I think this approach (pubishing an Excel workbook to Power BI) is equivalent to uploading an Excel workbook from the Power BI service (as described in the orginal post). Either way the same problem arises. Where the data source is a Power BI data model you cannot refresh the data from within the Power BI workspace