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
Anonymous
Not applicable

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

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. 😊

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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

master-files.png

 

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

file-cust-col.png

 

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

files-pq.png

 

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

5 REPLIES 5
PhilipTreacy
Super User
Super User

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

master-files.png

 

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

file-cust-col.png

 

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

files-pq.png

 

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

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
Super User
Super User

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.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

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
Not applicable

Bump

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.