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
MrToast20
Frequent Visitor

Get data from multiple excel files with multiple sheets

Hi there,

 

Fairly new to PowerBI and looking for some help.

 

I have around 60 excel files structured as follows:

 

Filename: Apr 20 Sheets: 2017, 2018, 2019, 2020

Filename: Apr 19 Sheets: 2017, 2018, 2019

Filename: Apr 18 Sheets: 2017, 2018

Filename: Apr 17 Sheets: 2017

 

These are point in time files.  So the "Apr 17" file incudes all data for the 2017 year asat 30/04/17.  The "Apr 19" file also contains data relating to the 2017 year, but is asat 30/04/19

 

Here's what i need to do;

  1. Combine all files and sheets into a single table. 
  2. I want to add a new column called year, this should take the sheetname as the year and populate that value for each record.
  3. I want to add a new column called Date_Loaded which sets the value to be the last day of the month, it should take the year in the filename.  E.g 30/04/17 for the "Apr 17" file, 30/04/18 for the "Apr 18" file etc
  4. When a new file is dropped into the folder, it should repeat the steps above, combining the data and adding the custom columns.

Help would be much appreciated.

 

Thanks

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @MrToast20 

All of this is possible using Power Query, please refer to this article to give you an example of how to do it

https://www.myonlinetraininghub.com/import-multiple-files-containing-multiple-sheets-with-power-quer...

Regards

Phil



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

1 REPLY 1
PhilipTreacy
Super User
Super User

Hi @MrToast20 

All of this is possible using Power Query, please refer to this article to give you an example of how to do it

https://www.myonlinetraininghub.com/import-multiple-files-containing-multiple-sheets-with-power-quer...

Regards

Phil



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!


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.