Forum Discussion

Kratchie's avatar
Kratchie
Frequent Visitor
3 years ago
Solved

Automatically import multiple Excel files by Month/Year in filename

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? 

 

 

 

Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    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-94b8023c-2e66-4f6b-8c78-6a00041c90e4 

    Please let me know if you have more questions.

2 Replies

  • jennratten's avatar
    jennratten
    Super User
    3 years ago

    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-94b8023c-2e66-4f6b-8c78-6a00041c90e4 

    Please let me know if you have more questions.