Get data from multiple Excel files using the file path as parameter

[Anonymous]

Hi everyone!

I have a problem i haven't been able to find the solution for.

I have a bunch of Excel files located throughout different folders. Each file pertain to a specific project, and have the same data structure. They all have a table on the first page with the same columns and a single row of data about the specific project.

To find the different Excel files i have created a "Master" Excel which have a table of all the file paths.

The idea is to use this "Master" in PBI to find all the Excel files, grab the rows of data and put them together in one table in PBI.

Then when a new project pops up, and a new Excel file is made, i can simply add the file path to the "Master" Excel and let it grab the new data and add it to the rest.

How would i go about doing this?

If you need more info let me know. 😊

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

What I meant was that the Master file you created will have a list of the files you want to load data from, something like this

Create a table from this data (CTRL+T) then load into Power Query/Power BI.  Add a custom column like this

which will give you the contents of each file in that new column (as tables) like so

You can then expand these tables (click on the double haeded arrow in the header) and extract the data you want.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[dmitriiv]

What if those Excel files have multiple sheets, how to correctly add the sheet name to the path?

[Anonymous]

Hi @PhilipTreacy 

I managed to figure it out a second before your reply. 😄

I was having trouble getting the data that i wanted, but that was simply an error within the Excel files, which i fixed.

Thanks for your help!!

[PhilipTreacy]

Hi @Anonymous 

Load the Master table of file paths/names into Power Query.

Add a Custom Column with the following

= Excel.Workbook(File.Contents([Filepath]), null, true))

This will load the contents of the files into a new column.  Expand/transform the files as needed.

regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi @PhilipTreacy 

I'm afraid i dont entirely understand what you mean by loading the table into Power Query.

Could you elaborate a bit? 🙂

[Anonymous]

Bump