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

Import Excel files from Folder automatically for every month

Hello all,

What exactly that iam trying to ask is,

 

I have a folder in my local system which is having excel files, containing perticular month(per suppose current month) data with name ex: Data_Apr_2017.

And this excel file having multiple sheets like sheet1, sheet2, sheet3.

Like that by next month i ll be getting the file with name as Data_May_2017 with mulitple sheets as previuous one like sheet1, sheet2, sheet3...Like that i ll be getting files for everymonth.

For everymonth i ll be getting the data with the same datatype except the name of the file changes as per month and it contains only that perticular month data.

Here I have below things to know,

1:-is there any way to Get the files and load into powerbi from a folder automatically, whenever a new file is added.

2:-If it is not possible with folder type, is it possible by sharepoint or OneDrive.

3:- As i mentioned, for each month file contains only that perticular month data,then is can we append those multiple months data by differentaiting with month, by adding another column as Month.

Please have a look at the below images for this question.

1st month data1st month datait should load into powerbi by adding another column Month with current monthit should load into powerbi by adding another column Month with current month2nd month data2nd month dataBoth months data with diff month valuesBoth months data with diff month valuesI would like to know whether is there any automation for this rather than doing manually.

Please feel free to ask if you need any furthor information.

 

Any help will be appreciable.

Thank you.

Mohan V

 

 

 

3 REPLIES 3
MarcelBeug
Community Champion
Community Champion

A few questions:

  1. Which data - in your examples - comes from sheet1, sheet2, sheet3?
    Or do you only use data from 1 sheet?
  2. Did you try "Get files from folder" already and - if so - where did you get stuck?
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Thanks for the reply @MarcelBeug.

1:-In my scenario sheet1 contains revenue data, sheet2= Contribution, sheet3=Sales.

Three of those sheets gets data for every month as i mentioned above.

 

2:- I have tried using get folder and it works fine loading multiple files at a time.

And it works fine, if we change anything or add anything in altready imported those sheets into power bi,whenever we refresh it.

But if any files which are added with new name otherthan the previuos loaded files,then is there anything like to load automatically whenever we place a new file into that folder.

 

If you get files from a folder, then you will get all files in that folder and any subfolders. 

It is good practice to filter on the result, but you should filter in such a way that new files will still be included.

E.g. in your case you might want to select all files with extension .xlsx and a name starting with "Data_", so if any Data_ file is added to the folder, then it will be included in the import.

 

You can get the month part from the filename by using the new Text.BetweenDelimiters function, e.g.
Text.BetweenDelimiters("Data_Apr_2017","_","_")

gives you "Apr".

 

Specializing in Power Query Formula Language (M)

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.