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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors