Refresh of Excel workbooks in App Workspace

[edwardrmiles]

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?

To replicate..

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

[edwardrmiles]

This idea is very close to the solution I'm looking for:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11040396-add-ability-for-excel-wo...

It's been planned, since March 2016...

[Anonymous]

@edwardrmiles, you might look at using the Power BI publisher for Excel add-in. It can be found here:

https://docs.microsoft.com/en-us/power-bi/publisher-for-excel

[edwardrmiles]

@Anonymous 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? 

[Anonymous]

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

[edwardrmiles]

@Anonymous 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?

[Anonymous]

@edwardrmiles

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

[edwardrmiles]

@Anonymous 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

[Anonymous]

@edwardrmiles, we currently have this feature disabled in our tenant. However, I have the old workbook that was published before the feature was disabled. You should see the following button to schedule a refresh from the Excel Workbook.

You should from here be able to setup the on-premise gateway refresh. Also, here is some more information regarding the refresh: https://docs.microsoft.com/en-us/power-bi/service-gateway-personal-mode#limitations-and-consideratio...

[edwardrmiles]

@Anonymous unfortunately that isn't working when the Power BI model is the data source for the Excel workbook.  I see this message...

[augustindelaf]

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

Best Regards.

[edwardrmiles]

@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

[augustindelaf]

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

BR,

Augustin

[edwardrmiles]

@augustindelaf I'm not sure this will solve the problem. I'm not talking about using Excel as a data source. I'm talking about refreshing Excel workbooks that use a Power BI model as their data source - from within the Power BI service (i.e. the workbooks area of a workspace)