Automatically import multiple Excel files by Month/Year in filename

[Kratchie]

Hi all,

Trying to find a good solution to import reports depending on filename (Month/Year)  and it starts from January 2022 for example.

Filename: Report January 2022.xls

Then next months should be automatically imported 1st each month like Report February 2022.xls, Report March 2022.xls and all reports should be merged together.

Is it possible? 

[Kratchie]

Hi @jennratten, sorry for the late reply! Thank you very much for the solution.

[jennratten · Accepted solution]

Hello!  You can use the Get Files from Folder connector and choose Transform and Combine.  Then Power Query will set this up in the query editor and combine the data from the files for you.  It's best to have a folder dedicated to only the files that need to be combine for this purpose, but if that's not possible, you'll need to come up with some logic that filters the list of files so that only those needing to be combine remain in the list before they are actually combined.

Power Query will create these for you:

Transformations that need to be made in all files and can be done before the data is combine should be done in the Transform Sample File table (#1) and transformations that need to be done after the data is combine should be added to the additional table (#2).  

Here is a link to the documentation that lists the steps for importing data from multiple files in a folder.

https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query... 

Please let me know if you have more questions.