Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Dan Quigg]

I currently use Dropbox as a source for my Access databases. Currently, I hardcode the file source path and am trying to find a way to avoid doing that. One thing I tried was to specify the file path as a parameter.  That seems to work in Power Query but when I close and apply it, it puts a lock on the database and cannot refresh the model which then cascades through the rest. 

Does anyone know of a way to avoid this or another way to specify the file source path in one place other than as a parameter.  I also keep the PBIX files in the same folder as the source DB so maybe another way to automatically assume the source data is in the same folder. 

Thanks

——————————
Dan Quigg
CEO
Public Insight Corporation
Hudson OH
330-656-9201
——————————

[Ken Puls]

Unfortunately, I don’t know any real way to pull this off with Power BI Desktop dynamically.  It just doesn’t have a method to be able to read the file path to the solution to make it dynamic.  (I really wish I had better news here.)

In Excel you can actually get around this using the CELL() function or – in the case of Dropbox – with a bit of VBA.  I actually wrote up an article on it that you can find here:  Sharing Power Query Solutions

The Ken Puls (Excelguru) Blog		remove preview
Sharing Power Query Solutions One of the attendees at today’s Power Query Workshop asked about a specific scenario today. His company doesn’t have a solid IT infrastructure for sharing data, and he’s trying to figure out the best method for sharing Power Query solutions with a colleague so that he’s not the only one with the power to update. View this on The Ken Puls (Excelguru) Blog >

——————————
Ken Puls
——————————
——————————————-

[Dan Quigg]

Ken,

Thank you for this and I saw this as a potential option previously.  I may end up going that path.  It is strange that parameters seem to work great until I close and apply the session in which case it locks the file and all of the other connections fail.  I may try turning parallel loading off (just saw that option yesterday) and see if that works as well.

——————————
Dan Quigg
CEO
Hudson OH
330-656-9201
——————————
——————————————-

[Meagan Longoria]

I don’t use Access databases, so I’m not sure about the lock. But another way to specify connection info only once is to put the file path in a query and reference it from other queries. Chris Webb shows how to do this for SQL connections here:

https://blog.crossjoin.co.uk/2015/11/09/avoiding-duplication-of-database-connection-information-in-power-bi/. You could do something similar but would only need one query to store the full path rather than the two Chris used.

If you do this and you have layers of queries that you have merged/appended/referenced, you might run into the issue described by Ken Puls in http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/. He tells you how to redesign it to work around the issue.

I don’t know if this behaves any different than using a parameter, but it might be worth a try.

——Original Message——

I currently use Dropbox as a source for my Access databases. Currently, I hardcode the file source path and am trying to find a way to avoid doing that. One thing I tried was to specify the file path as a parameter.  That seems to work in Power Query but when I close and apply it, it puts a lock on the database and cannot refresh the model which then cascades through the rest. 

Does anyone know of a way to avoid this or another way to specify the file source path in one place other than as a parameter.  I also keep the PBIX files in the same folder as the source DB so maybe another way to automatically assume the source data is in the same folder. 

Thanks

——————————
Dan Quigg
CEO
Public Insight Corporation
Hudson OH
330-656-9201
——————————

[Dan Quigg]

Thank you Meagan!

——————————
Dan Quigg
CEO
Hudson OH
330-656-9201
——————————
——————————————-

[Blake Swanson]

Dan,

I can’t comment on Dropbox, but we use SharePoint (included in our office 365 subscription) to host our access DB files and the connectivity seems to work as expected. One note though, I’ve only been able to get the refresh to work for powerbi web service with a 64bit access file, though desktop powerbi can refresh both 32 and 64 bit access files regardless of the bit of powerbi desktop.

-Blake Swanson

CFO

Swanson Homes

——Original Message——

I currently use Dropbox as a source for my Access databases. Currently, I hardcode the file source path and am trying to find a way to avoid doing that. One thing I tried was to specify the file path as a parameter.  That seems to work in Power Query but when I close and apply it, it puts a lock on the database and cannot refresh the model which then cascades through the rest. 

Does anyone know of a way to avoid this or another way to specify the file source path in one place other than as a parameter.  I also keep the PBIX files in the same folder as the source DB so maybe another way to automatically assume the source data is in the same folder. 

Thanks

——————————
Dan Quigg
CEO
Public Insight Corporation
Hudson OH
330-656-9201
——————————

[Dan Quigg]

Thanks Blake!

——————————
Dan Quigg
CEO
Hudson OH
330-656-9201
——————————
——————————————-