Automating Weekly Excel Data Updates in Power BI

[JessseZ]

Hello, I am a new Power BI user. Every week, I receive an Excel file from our tool inventory system, which is saved in a local folder on our server. This file serves as the data source for a Power BI report that I manually update and publish to make it accessible to specific individuals.

The Excel file's content changes weekly, but the layout remains consistent. What I would like to achieve is the following: when the file is generated and uploaded to the local folder on Monday morning, it is automatically sent to, for example, SharePoint (or another platform). From there, I want to create a flow to enable Power BI to automatically update the report. How can i do this?

I tried something with power automate but they dont seem to work.

I would love to hear your tips or possible alternatives!

[Anonymous]

Thank you lbendlin 

Hi, @JessseZ 

I think you can use a file from OneDrive or SharePoint Online to build your report, then publish it in Power BI Service, and configure OneDrive refresh. Whenever your file data is updated, this will update the data in Power BI:

Refresh a Power BI Desktop File (pbix file) from OneDrive or SharePoint Online - Power BI | Microsof...

Data refresh in Power BI - Power BI | Microsoft Learn

Best Regards

Jianpeng Li

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

[JessseZ]

Hi Jianpeng, 

Yes, but I first need to set up an automatic upload from our local folder to OneDrive or SharePoint. The data in the Excel file itself doesn’t change, but a completely new Excel file with updated data is added each time. Therefore, the file path or name needs to automatically update to reflect the most recently added file.

[Anonymous · Accepted solution]

Hi, @JessseZ 

It is possible that you need to import the entire SharePoint folder. Here I take the local folder as an example:

At first I only had two excels:

Now let's test if my data will be updated automatically if I add a new excel:

You can follow the steps above, apply it to the Sharepoint folder, and then configure a scheduled refresh for your semantic model.
Whenever you have a new structure with the same excel, they will automatically update the data.

Best Regards

Jianpeng Li

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

[lbendlin]

Every week, I receive an Excel file from our tool inventory system

How do you receive the file?  Via email attachment?

[JessseZ]

Hi Ibendlin,

The file is exported directly to a designated folder on our server, without the use of email.