Dynamic path to the folder with the excel files that I combine together in Power Query

[Simon_29]

Hello,

I can create a dynamic path to a folder and a selected file in Power Query. I can change FileSource and FilePath dynamically.

However, this time I load multiple excel files from one folder and combine (append) them together. I would need to create a cell in Excel that would serve as a place where I insert the PATH to the folder and when I refresh the power query, it will automatically load all the files under it as shown in the screenshot below.

Can you please help me? Thank you very much

[PhilippeMuniesa]

good evening,
as far as I'm concerned, and as I mainly work with excel, I use macros in vba, which allow you to open explorer, and select the desired folder, and drop the path in a named range of the workbook, whether powerbi or powerquery will come retrieve as parameter. If you are interested, I can drop you an example of macro.

let me know then

good luck

philipp-e

[Daryl-Lynch-Bzy]

Hi @Simon_29 - yes this is possible.  Please try the following as it will create to string values that can be combined into replace the path and folder string in your example:

1. Create a worksheet called "INPUT"
2. In Cell A1 type "Enter Path:" and in A2 type "Enter Folder:"
3. In Cell B1 type in the path value, and in B2 type in the folder value
4. Use the Named Range features in Excel to call cell B1 "Input_Path", and cell B2 "Input_folder"
5. Select B1 and use the "From Table/Range" to add "Input_Path" into Power Query
   
6. The trick is to add "{0}[Column1]" to the function to show the path and folder strings

let
    Source = Excel.CurrentWorkbook(){[Name="Input_Path"]}[Content]{0}[Column1]
in
    Source