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.

 

[pjmorris]

This works for me:

A table with the name 'tblPresets', the filename is in row 0.

Preset	Value
Filename	P:\Users\A Folder\A file
Records Start	42000
Count of Records	1000

The Powerquery code to use it is as follows:

    Presets = Excel.CurrentWorkbook(){[Name="tblPresets"]}[Content],
    Location = #"Presets"{0}[Value],

    Source = Excel.Workbook(File.Contents(Location), null, true),

Hope that helps.

 

[ExcelAtEverything]

Worked perfectly using named range. Thanks again Alan!

 

[pjmorris]

Alan? no matter.

Regardless of who's solution worked please mark it as an answer as that will help other users.

Thanks.

 

[ExcelAtEverything]

Alan? no matter.

Regardless of who's solution worked please mark it as an answer as that will help other users.

Thanks.

Sorry PJMorris. I didn't quite understand your solution, which is likely a reflection of my experience not matching that of yours. Yours could be better and much easier, but I'm a conceptual guy. I often need to understand the concept before I can grasp the happenings in order to implement into my project effectively. Since my original post here I have since gained a lot more working knowledge of how to create & use parameters. His worked for me because of how it was written. I instantly grasped how it worked and all the steps that went into it, so I was able to quickly make it work. I am super-appreciative of your response though PJ! Anyone else reading may want to give your solution a try as well.