How to Create a Dynamic File Path in Excel Power Query

How to Create a Dynamic File Path in Power Query

Pulling some information from different data sources is relatively easy nowadays. Thanks to Power Query as you don’t need to do it manually anymore or guessing an overwhelming VBA script just to get the right code for your data. Admit it or not, you’re investing so much time in searching for the formula/script in the internet and debugging errors just to get the task done.

This 10-minute video tutorial will help you how to make your data sources dynamic with the use of simple M script that you can add to refer to a named range in Excel. Let’s embrace this opportunity to learn new things.

Modify M Code:
Path = Excel.CurrentWorkbook(){[Name="Path"]}[Content]{0}[Column1],
Filename = Excel.CurrentWorkbook(){[Name="Filename"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(Path & Filename), null, true),

Practice File:

"Creativity is the power to connect the seemingly unconnected."
- William Plomer

*************************************************
Want to learn more about Excel?

SUBSCRIBE NOW:

YouTube Playlist:

See you there!

Romeo Costillas
Microsoft Certified Solutions Associate BI Reporting
Microsoft Office Specialist Master
Microsoft Office Specialist Excel Expert
Microsoft Innovative Educator
Discrete Event Simulation Modeler

Tagalog Excel | Filipino | Pinoy Excel | Excel Tagalog Tutorial | Paano Gamitin ang Excel

#excelbyromeocostillas #Excel #ExcelTips

[ks]

Quite a few methods I came across about dynamic file path on youtube but this one is the best. This works without defining an additional table. Good Job!

[inderjeetsaini]

My question is my file name keep changing every week since i get the updated data on weekly basis, so my file name keeps changing like ABC_2023_v1, so next week it will be v2, v3 and thorugh out the year this will repeat. So i need to just change the version number in the cell and power query should pick the data from the particular file every week. Could you please advise.

[inderjeetsaini]

I tried the same thing, but got an error say Key does not exist. I did defined the Filepath and Filename and used the same thing in the Advance Editor, but am not getting the desired result. any help?

[m.n.]

thanks for nice info . i have a question please? your path is started with
supose you have tow pcs or more and every pc the user name is not rcostillas in the aother pcs` is thier common name in vba that point to the user زIs there a general way to name the path with a generic username regardless of the name actually written on the computer?
its good for files that containg 5 or more codes that points to a specefic path in the computer and share this file to more than one person to save on thier pcs`.

[iifim]

How to change power query's source of data through VBA?

[ramyasrireddykasu]

Hi. I have a problem. When i make any changes in the source table and while saving it shows an error of sharing violation. Please help me with the solution.

[SAMMandKIMMsAdventures]

thanks for sharing... pede ba yun auto refresh?

[kumato]

There is almost nothing dynamic about this:
1. FilePath is not dynamic, you just copy and paste the path.
If you send the video file to another user it will not work because it is a different user.

2. FileName is also not dynamic because you have to write the files name in "Data Validation".
If I add a new file to the data source your FileName list is not updated automatically.
It only recognizes file names that you have MANUALLY typed in "Data Validation".

3. The music isn't dynamic either, but it's cool. Not the rest.