How to achieve easy file update on Powerbi repository

[Ruka]

Hi,

I am new to powebi and I am trying to understand if powerbi could be the answer to a challenge that I have.

I receive a monthly excel report file (always with the same format and schema) from a partner. I would like to have a dashboard that visualised the report content. I can achieve this when I link the one file with powerbi, however, I am not sure how to handle the monthly increments. 

Do I need to have some kind of master file that every month I manually update with the data from the new file that I receive, or is there a smart solution where I can just drop the file into a repository and Powerbi will automatically fetch all files in the repository?

Any ideas are welcome.

Thanks

[Anonymous]

Hi @Ruka 

Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?

Best Regards,

Jayleny

[FarhanJeelani · Accepted solution]

Hi @Ruka ,

Yes, there is a smart solution! You don’t need to manually update a master file every month. Power BI can automatically pull data from a folder where you drop your monthly Excel reports.

Solution: Use a Folder as a Data Source

Here’s a step-by-step guide:

1. Organize Your Files

• Save all your monthly Excel reports in one folder (e.g., C:\Reports\MonthlyReports or a cloud repository like SharePoint, OneDrive, or a network drive).

2. Load Data from a Folder in Power BI

1. Power BI Desktop > Home > Get Data > Folder.
2. Select the folder where your monthly files are stored.
3. Power BI will import all files from that folder.
4. In the Navigator window, Combine and Transform Data.
5. Power Query Editor will open and automatically combine all files with the same structure.
6. Apply any necessary transformations (like removing unnecessary columns or cleaning data).

3. Set Up Automatic Refresh

• Publish your Power BI report to the Power BI Service.
• Go to Power BI Service > Dataset Settings > Scheduled Refresh.
• Connect to your folder source (if it’s on SharePoint or OneDrive) and set up your refresh schedule (daily, weekly, etc.).

4. Monthly Process

• Every month, just drop the new Excel report into the folder.
• Power BI will automatically pick it up during the next refresh.

Benefits of This Approach

• No manual data merging every month.
• Automated refresh ensures your dashboard is always up-to-date.
• Handles multiple files seamlessly as long as they have the same structure.

Please mark this as solution if it helps you. Appreciate Kudos.