cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gavin007
Helper IV
Helper IV

Share Excel file with BI capability in Sharepoint or Onedrive

I have an Excel file connected with Power Bi dataset. I want to share with a group of branch managers who need to refresh the pivot table and do some manual input to come up with a consolidated forecast. Currently I have to either create one file for branch and a master file to consolidate the input, or I have just use create a branch tab and consolidated tab in one Excel file but the file can't be shared as it will lose the Bi connection.

 

Either way, it is not a sleek solution. I then think about putting the Excel in Onedrive or Sharepoint solution.  I did some research, my question is, 

1. which one is better, Onedrive or Sharepoint?

2.  does it mean each user has to has the same AD log in Onedrive or Sharepoint  as Power BI in order to refresh the data?

3. if I have Onedrive or Sharepoint subscription and the other don't. Can i still let them has the read and write access to the file? I assume they can't refresh the data since they only have Power Bi subscription not Onedrive or Sharepoint?

 

I am new to Onedrive or Sharpoint in terms of Direct Query in Excel. hope i explain enough here.

 

 

 

 

7 REPLIES 7
v-xulin-mstf
Community Support
Community Support

Hi @gavin007,

 

About how to refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online, please refer:

v-xulin-mstf_0-1619574037511.png

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-excel-file-onedrive

 

Best Regards,
Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

lbendlin
Super User III
Super User III

"Analyze in Excel Online"  is not yet available - wait until after MBAS.  

 

Or are you saying that you manually connected to a Power BI Dataset from Excel via the SSAS option?

Mine is "Analyze in Excel" and want to "Analyze in Excel Online". My current Excel 2013 need to install an add on to do it, I thought Excel 365 online doesn't need that but looks like I have to wait.

 

I dont use SSAS. But are you saving Excel report based on SSAS data can be refreshed in onedrive or sharepoint?

lbendlin
Super User III
Super User III

Please define what you mean by "refresh the Excel data" - are you expecting them to manually enter data into an Excel spreadsheet? Or are you expecting them to refresh that Excel file from existing data connection in that file? If so, what are these connections pointing to?

Refreshing the pivot table that is connected to power bi dateset for actual, budget etc.  then base on the data, They will need to manually enter the forecast number in a seperate table. Hence there is no write back required. 

lbendlin
Super User III
Super User III

1. they are functionally equivalent

2. Not relevant - they cannot "refresh the data"  the way you imagine. Data write back from Power BI into the data source is not fully implemented yet.

3. Not relevant yet.  May change  after MBAS.

@lbendlin Thanks for the reply.  here is my further comments.

 

2. I don't need to write back from Pbi. I am wandering if onedrive or sharepoint require the same AD login as Power Bi to refresh the Excel data.

 

3. If question 2 answer is no,  when the power bi user want to refresh the Excel data shared in Onedrive or Sharepoint, do they need to have a subscription on Onedrive or Shareporint?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors