How to import several Excel files based on another Excel file?

[Sica]

I have an Excel file which contains the name and location of several other Excel files (which have the same format, but are located in different places).

Is there a way to read through my Excel file (the one containing the file names and paths), and append all files from this list into a new query?!?

Thanks.

[AlexisOlson · Accepted solution]

You could load your list of file paths into Power Query and then add a custom column like this:

Excel.Workbook(File.Contents([Column1]), null, true){[Item="Sheet1",Kind="Sheet"]}[Data]

Then expand that column.

This is just an example. If you want specific tables instead of loading Sheet1 or you need to promote headers or whatever you'll need to adjust accordingly.