Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Resident Rockstar
Resident Rockstar

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors