cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Simon_29
Helper II
Helper II

Dynamic 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

FolderPath_PQ.png

2 REPLIES 2
PhilippeMuniesa
Resolver I
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

Daryl-Lynch-Bzy
Super User
Super User

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
    DarylLynchBzy_0-1673627934561.png

     

  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

 

 

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors