Help replacing Source Folder Location of Query w/ Parameter

[ExcelAtEverything]

Hello Excel Jedis,

I'd like to make the location of the source folder of my query dynamic, so that the folder location can be changed easily without need to go into Power Query. I created a user table on a new sheet where the user can paste in the location of the source folder on their computer. A query then pulls that into PQ where I drill down & create a parameter out of it (called FolderPath). Here is a pic of how that cell in the table looks when a folder location is pasted into it (which of course is exactly how the parameter appears in PQ). The cell contains the path of the folder which holds all of the needed spreadsheets to make this sheet work.
Table entry:

I'd like to use the parameter FolderPath in my source code line of the query, but I can't seem to get it correct.
Here is the original source line:

Is it as simple as getting the syntax correct, or is there more to it than just replacing it with the parameter FolderPath? Sorry I can't send in XL2BB right now. Thanks for help.

 

[alansidman]

You need to replace the path in your Source statement with the name of the table you created that holds the variable path name.

 

[ExcelAtEverything]

You need to replace the path in your Source statement with the name of the table you created that holds the variable path name.

Replace it with the name of the table, and not the name of the parameter I created using that table?

 

[ExcelAtEverything]

I tried replacing with the table name, and got this error:
Expression.Error: The import FolderPathTable matches no exports. Did you miss a module reference?

 

[ExcelAtEverything]

You need to replace the path in your Source statement with the name of the table you created that holds the variable path name.

I replied originally without replying to your suggestion, so redoing here.
I tried replacing with the table name, and got this error:
"Expression.Error: The import FolderPathTable matches no exports. Did you miss a module reference?"

 

[alansidman]

Did you save the new Path Table/Query to Connection only? Does this query have the same name as the table? My error, use the query name.

Create Dynamic File Path in Power Query - Goodly

Create a dynamic folder or a file path, both in Excel Power Query and in Power BI. Create Dynamic File Source in Power Query

www.goodly.co.in

 

[ExcelAtEverything]

The new path table/query is brought into PQ & converted to a parameter named FolderPath, so saving to connection only is not an option, unless you can bestow some knowledge here that I am unaware of. I went back & adjusted so that now the table & the Parameter share the same name of FolderPath. And that is the problem I'm having. I'm changing the path in the source statement to this, and I'm getting this Formula Firewall error. I've been reading & watching videos on it all day, but can't seem to get anything to work.

let
    Source = Folder.Files(FolderPath),

 

[alansidman]

The parameter table needs to be saved as connection. How else can the primary table find the information if it is not saved/loaded?

Look at this video that clarifies what I apparently am not communicating clearly

 

[ExcelAtEverything]

The parameter table needs to be saved as connection. How else can the primary table find the information if it is not saved/loaded?

Look at this video that clarifies what I apparently am not communicating clearly

Thanks. I watched this video like 3 times yesterday (& 2 more times just now) trying to get this to work and I simply cannot get it to work like he has it working. Those added M formulas he creates & pastes in either error out for me, or I get the same error as before. Plus, I could be mistaken but it looks as if his whole file path structure may be different than mine, which I'm guessing is why. I have a folder which has 4 files inside of it each day. Those files are changed/updated every morning with the latest numbers, but nevertheless everyday there are 4 updated files with inventory data from 4 different stores. He seems to have the back end of the file path (the part including the file names themselves, and adding them to his path. But my files can't do that because the names change each day (because they names always end in yesterday's date. I simply need to be able to change the path of the folder itself, so that whoever is using this will be able to just continue dropping those files into that same folder, but on their own desktop someplace. As for the video, the part is right around 3:20 where he begins talking about doing what it is I'm trying to do, but it just seems like maybe his folder/files are setup a little different than my situation. Or maybe I'm completely crazy, which is absolutely possible, Alan. I feel like this can't possibly be this difficult for me to do here, especially given that I've done it once before a while back, and I don't recall it being nearly this difficult for me to figure out.

 

[alansidman]

See if this one is easier to follow. It is done with named ranges.

Power Query dynamic file path | Excel Quick Help

Learn how to create dynamic file paths for Power Query data sources, using cell references from another worksheet.

excelquick.com