Error with refreshing query from shared file via Dropbox

We are attempting to refresh data into a shared spreadsheet from a shared spreadsheet source via Dropbox, but we are receiving an error message in Excel when clicking the refresh option that states [DataSource.error] Could not find part of path.

At first, we didn't have the Dropbox mapping on our local laptops named the same and were previously getting an error stating it couldn't find the file in question as it referenced a different directory structure between laptops synching from Dropbox. We corrected that issue and created a common directory off our respective C:\ drives so they would match, but now we are getting this error stated above.

The other person sharing this file can't refresh my queries when opening the same shared file on his end but can create a new query based on the same shared input file just fine. On the other side, I can't refresh queries he developed when working with the file on his PC that is ultimately synched to Dropbox and back to my laptop.

We looked at the advanced settings and both appear to show the exact same directory structure referenced between the queries built in the same Excel file shared across the two laptops.

Is there some other setting within Power Query that I am missing?

Thanks in advance for any input or questions back for me.

Have a read of this article and see if it helps: http://www.excelguru.ca/blog/2015/09/23/sharing-power-query-solutions/

The biggest challenge is around the user mapping path, as you stated. This post should give you the ability to dynamically read what that path is.

Thank you for sending this helpful information over...

I do have a few follow-up questions to ensure I understand where everything goes:

We have two files across different folder structures being leveraged with one being the data source of information and the other being the summary spreadsheet containing the Power Queries developed to pull different pieces of information. I understand the concept of creating the various parameters to pull in the needed user and folder path data but then I wanted to confirm your article on building the parameter for Power Query. Currently, the summary spreadsheet contains a series of queries with each pointing to the same source file in question that is shared across different dropbox users. I know the intent is the dynamically pull in the path versus having it "hard coded" in query when selecting the new query from file option created from my laptop.

1. Does the parameter table and parameter function referenced in your article get created in the data source file from which you will be pulling the data from so it has the valid path for the summary table to reference no matter who is access that file
?

For example from one of my current queries:
Source = Excel.Workbook(File.Contents("C:\Wgal\Dropbox\Wranglerette - JanJub - Tally and Scoring\Current JanJub Competition\Data Entry\Ensemble_Officer_Team\DATA ENTRY - JJ Ensemble_Officer_Team - Master.xlsx"), null, true),


I am just confused on how the summary file is ultimately referenced to the Data Source if it parameterized vs. being directly "pointed" to the file by selecting the file name when building the query.

Thanks in advance for any other insights or clarifications as the information provided was helpful and I just want to ensure I know how it all should link together.

Generally what I do is set up a solution file in one folder (this is the file with my power queries). I then create a sub folder for all my data files.
In the solution file, my dynamic file path read the solution folder path, no matter where it is opened from, and I appended the data file path to that to create the full data path structure.

Does that makes sense?


Sent from my iPhone using Tapatalk