Best Practices Advice for Automated Refresh Excel CSV

[Anonymous]

The goal is to receive a weekly Excel CSV file and have it on an automated refresh schedule. After some investigation, I understand how to load a file into PBIX but I am confused on the process if a new file will be sent weekly. How would you approach if the file name is changed everytime? (Ex: excelfile1.csv / excelfile2.csv / excelfile3.csv / etc...). How would you approach if the weekly delivered file name is the same everytime? (Ex: excelfile.csv) 

Most of the post/videos that discuss Excel file integration into PBIX discuss loading 1 file. I am more confused on how you would do it on a consistent weekly basis. I am hoping there is a way for the new weekly delivered file to be dropped into a teams/onedrive folder and the scheduled refresh will ingest it without any manual updates/file merging. 

If the request seems very amature, apologies, as this is my first time working with this type of project request. If there are post/videos that describe this specific scenario, feel free to drop the link. Your advice/suggestions on best practices are greatly appreciated. 

[Ritaf1983 · Accepted solution]

Hi @Anonymous 

To automate the process of loading weekly Excel files into Power BI, you can use a folder as your data source. This method works whether the file names change or remain the same, as long as the data structure stays consistent. You can store these files in OneDrive for Business or SharePoint, and Power BI will automatically update during scheduled refreshes.

1. Store your weekly Excel files in a folder on OneDrive for Business or SharePoint.
2. In Power BI Desktop, go to Get Data > Folder, and select the folder location.
3. Power BI will combine the data from all files in the folder, and you can transform the data in Power Query if needed.
4. Set up automatic refresh in the Power BI Service to pick up new files added to the folder.

Helpful Tutorial Videos:
- https://www.youtube.com/watch?v=x30ssa2K9gU
- https://www.youtube.com/watch?v=4IaQb3KkcjI
- https://www.youtube.com/watch?v=NISsW-bVAwU

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

[audreygerred]

Hi! Create a folder in a SharePoint site. Make sure that each csv file follows the same schema as far as field names (doesn't matter that every file name is different). Then, you will use the SharePoint folder connector and find the folder you need, then you can have the steps to combine files. This way every time you do a refresh it it'll append all the files from that folder together as one 🙂

Here is an article with details on geting this set up: Power Query SharePoint folder connector - Power Query | Microsoft Learn