Sharing Files with Power Query Parameter Feature

When you are sharing Power Query files, it's usually not an issue to update and refresh your data. However if your source file and your file that is the final output for analysis are separate files, it becomes an issue. You're sending two separate files to your co-workers and expect this to work? Good luck, unless you have an easy way to make your links to the source file link. That could be done fairly easily with a parameter. Check out the video to see how you can make sharing Power Query files seamless.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

🎁 If you find these videos useful and want to support my channel go to

#excel
#msexcel
#doughexcel

[bengodgundam]

Insanely useful!! This is the simplest way I have seen that does not involve complicating my boss with codes

[wayneedmondson]

Nice one Doug! Usually, I store the path in a named range on the worksheet and then use that as the parameter in my query. But, nice to know multiple options. Thumbs up!!

[purlsquinn]

Excellent overview. I'm building a query for someone, and I wanted them to easily change the path so they can load their proprietary data. This was a perfect example!

[lenac]

Hey Doug, I actually store the filepath as a cell in a table. Load it into power query and turn it into a list which gets picked up as a parameter which you showed in your demo. That saves me a couple of steps and time

[khadamich]

I was wondering if there is a way to do that automatically. Let's say Lisa has no idea of how to use Power Query. Is there any way I could do that? Like, update the path automatically because as you mention, most of the path is the same except by the users name. Soy maybe if I could have that relative path changing everything could be solve both tbh I dont know how to do it using Power Query

[jerrydellasala]

This is fine, but there are easier ways to get the path into a query.
Assuming both files are in the same directory, you can have a cell with the formula:

"=LEFT(CELL("filename"), FIND("[", CELL("filename"))-2)"

This will return the folder path without the trailing backslash of the current file. That cell can be put into a table and a query created using that table. Drill Down to the only row and column in the table and you get the path. Name the query FilePath and then use that the same as you show in the video. If you provide a sub folder for the source data and the other person uses the same data sub folder (such as extracting a Zip file with sub folders), the formula above can be easily set up to add that folder to the result of the function by adding "&"\DataFolder" to the formula.

Alternatively, if you don't know where the other person will put the source data file, you can provide a simple one row (one column) table that has the text of the source file's path that can be manually updated, and then used to provide the query the path to the file.

One warning about the CELL function. It reports the information (File Path in this case) of the most recently used workbook after the last recalc of any open workbook. Thus, if you have 2 workbooks open, and you do something that invokes a recalc and then switch back to the Workbook with the query, the cell with the formula above may have the wrong value. Hitting [F9] to recalc should update the value in the cell.

One other thing, I consume a lot of Excel videos, and they're always more enjoyable when you there are starting sample files to follow along with. A finished file is nice, especially with complicated formulas and queries, but not essential.

I really enjoy your videos, and hope you don't take this post the wrong way. I'd love to see you use this technique in another video! Thanks for all your work.