Forum Discussion
Helper IIDynamic path to the folder with the excel files that I combine together in Power Query
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
2 Replies
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:
- Create a worksheet called "INPUT"
- In Cell A1 type "Enter Path:" and in A2 type "Enter Folder:"
- In Cell B1 type in the path value, and in B2 type in the folder value
- Use the Named Range features in Excel to call cell B1 "Input_Path", and cell B2 "Input_folder"
- Select B1 and use the "From Table/Range" to add "Input_Path" into Power Query
- 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- PhilippeMuniesa
Resolver I
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
