Collaborate on a shared Power BI Desktop File Using as shared Excel file as Datasource

[ROKRI]

Hello!

I need to collaborate with a colleague using a shared PBI desktop file and a shared Excel file as the datasource. I know PBI desktop establishes a pathway to the excel file on my machine, and if I share the PBI desktop file with someone on another machine, they'll have to restablish the pathway on their end. My organization uses Box.com combined with O365 online so I'm hoping to avoid this step but have not found a work around with these services.

More specifically, the PBI Desktop file and Excel file in question are in a shared folder on Box.com, to which we both have access, and can edit the Excel file using O365 online. This Excel file is updated with new data every month and we both need to be able (hopefully) to work in the PBI Desktop file without changing the pathway each time.

Some possibilities I've seen include - adding datasets (the Excel file in question) in Power BI online service but it seems you can only edit PBI report in the service, not desktop, and it doesn't seeem you can easily update it monthly with new data; there seems to be the ability to do this through Sharepoint or Onedrive and I'm hoping to replicate similiar pathway to Box.com but can't get that to work;

I appreciate any recommendations. (I've combed through the forums and didn't find anything to resolve)

Thanks,

ROKRI

[AlexisOlson]

If you connect Power BI to the Excel file in the cloud, then you wouldn't need to point files on different machines.

[ROKRI]

Thanks for the guidance. Can you please provide more detail on what you mean? I read up on connecting PBI to Excel in the cloud but am confused. Here are the two pathways I see, though, don't fully understand how to make work:

1. Import Excel file into PBI online, which may be the cloud you reference and would keep it centralized. However, while I am able to import the excel file into PBI online, I don't understand how to connect it to an existing report nor how we would update it monthly. To update, we'd either need to copy/paste in new data or upload a new excel file over the existing one, right?  That said, if I'm on the right track, then the way this works regarding access is she can publish everything and share with me, or vice versa, and what ever changes one of us makes will be reflected in the same file (pbix or excel)?

2. If #1 isn't what you meant, I've also seen connecting via OneDrive Business, in the cloud, which my organization has but isn't our prime document storage.

Am I missing something? I appreciate your continued support.

ROKRI

[AlexisOlson]

The basic principle is that Power BI's data source should be in a fixed location. In your case, this is an Excel file. If you save it somewhere in the cloud where you both can access and update it, whether that is Google Drive, Dropbox, OneDrive, SharePoint, or whatever, that's where you point your Power BI file to load the data.

To update each month, you'd edit the Excel file in the cloud and then simply refresh your Power BI report in the desktop editor or the online service (it should work for both). I think this is your option #2.

[ROKRI]

Understood on the cloud, I appreciate the additional details, and along the lines of what I've been trying to do.  
I'm trying to do that through Box.com, a cloud based file storage solution similar to OneDrive but I can't get it to connect. I've tried using a publicly accessible box link to the file and just the main website to connect but it won't establish a connection to the dataset.

Any ideas specific to Box connectivity? I can't find any guidance on that after lots of research. 

Thanks,

ROKRI

[ROKRI]

Thank you @AlexisOlson , I have been looking for that answer for a looong time! Solved.

Your link took me to a response from @wardt with a link to the Box Direct Link, pasted here for ease of reference: "Use the "Direct Link" (direct download link) feature. It is not available for free personal box.com accounts. You can read more here: https://support.box.com/hc/en-us/articles/360043697554-Configuring-Individual-Shared-Link-Settings"

Answer is at the bottom of the page where the link takes you. So relieved...

ROKRI

[AlexisOlson · Accepted solution]

I can't speak to Box connectivity specifically but it has come up before:

https://community.powerbi.com/t5/Power-Query/box-com/m-p/119392