Connecting Powerquery to all folders within one folder

[Craigc3814]

Is there a way that I can connect powerquery to one main network folder, then pull in any excel file that is in that network folders sub folders?

I am trying to come up with a budgeting tool for my company, I want to give each cost center their own network folder that they can access to enter their budget data. Is using network folders even the best approach for this? There are about 50-60 cost centers total, I do not want to have to create a connection to each and every sub folder, it seems that that would be really slow and a bad method. Also if a new cost center is added I want it to be accounted for without re coding the tool.

 

[Ron Coderre]

OK....Here's what to do.
- Create a query to one Excel file and get the data from it (doesn't matter which file)
- Change the query name to fnGetFileData
- Edit the query in the Advanced Editor and make these changes
...Before the Let statement enter this and will list folders and subfolders and files:

(FilePath as text, FileName as text) =>

...Edit the Source step replacing it with this:

Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true)

Now you have a function that can get file contents using the FilePath and FileName parameters

Next, create a new query to get Folder Files and stop after selecting the main folder.
That query will have one step similar to this:
Source = Folder.Files("C:\Coderrer\Excel_PowerQuery")
- Add a Custom column to that query with these contents

= fnGetFileData([Folder Path] & [Name])

(you'll end up with a column named Custom that contains a series of Table objects
- Click the Expand icon at the top of the column
(That will expand every table and append them into one large table.)

Is that something you can work with?
(Post back if you have questions)

 

[Craigc3814]

I believe I can work with it, it looks like Ken Puls old method for combining workbooks before it became a feature.

I was searching around after I posted the question and found an interesting answer. I am going to test both.

let
Source = Folder.Files("C:\Users\xxxxxx\Desktop"),
FolderFilter= Table.SelectRows(Source, each [Folder Path] = "C:\Users\xxxxxx\Desktop\file" or [Folder Path] = "C:\Users\xxxxxx\Desktop\Read"),
NameFilter = Table.SelectRows(FolderFilter, each [Name] ="abc.xlsx" or [Name] ="cde.xlsx" )
in
NameFilter