How do I automatically update power bi desktop file with new excel files?

[james_wood]

Every month I update a large power bi desktop file with 10 static excel files taken from the CRM database. I manually import each file one by one into the powerbi file and then begin updating the analysis. This probably takes 20+ minutes to load everything in. 

Is there a way to automate this process and get the files imported with a single click of a button?

Note: the names of the excel files change each month with the month's name added to the end of the file name. So it's not just a case of uploading the same 10 excel files with the same name. Each month a new folder is created in OneDrive e.g. 'January 2023'. Then the 10 excel files are extracted from CRM in january 2023 with the suffix January 23 on them and copied to the folder. I copy last month's (December) Powerbi file to this January 2023 folder, rename the powerbi with a 'January 2023' suffix and then upload the 'January' versions of the excel files to this January PowerBi file to update for the current month. Hope this makes sense!

[v-yueyunzh-msft · Accepted solution]

Hi , @james_wood 

According to your description, you want to connect mutiply Excels to Power BI Desktop and it will be changed .

For your need , you can try to use Folder connector or SharePoint Folder Connector(If you want to consolidate, combine or append data from multiple excel spreadsheets) in Power Query .

For more information, you can refer to :
Combining Excel Files hosted on a SharePoint folder with Power BI | Microsoft Power BI Blog | Micros...

Power Query Folder connector - Power Query | Microsoft Learn

And after you connect the folder , you can selct the data you need and remove the data you do not need in Power Query!

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@james_wood , Using a folder is as the source is a better option here. Keep one type of file in one folder 

https://insightsoftware.com/blog/power-bi-load-data-from-folder/