Running query of monthly data

[Susan513]

I have a list in Excel currently. Like this:

Project	Report Date	SPI
Project A	3/31/2025	1.0
Project B	3/31/2025	1.2
Project A	2/28/2025	.90
Project B	2/28/2025	.95

There are many projects and lots of rows going back many months. Currently I have a power automate flow updating the excel for me. Essentially I need one row per project per report date. Sometimes the SPI data changes, and I run the flow again to make updates to the matching row, if the date is new, then it makes a new row, otherwise if the date exists it updates the existing row.

Is it possible to do something similar in Power BI, add and update data in a query? We are tracking trends over time. If I can automate this in Power BI it would make a world of difference - appreciate any ideas to do this.

[rohit1991]

Hi @Susan513

When using Excel in Import mode, Power BI doesn’t automatically detect monthly changes unless you manually refresh or publish again. This is mainly because Import mode doesn’t continuously check for updated data it just loads whatever is there at the time of publish.

You can try these steps to avoid mannual upload:

1. Store your Excel file in OneDrive or SharePoint Online
   Power BI can sync with those services and pick up changes automatically (roughly every hour). This way, once the Excel is updated on OneDrive/SharePoint, the dataset can refresh in the background  no need to republish the report every month.

2. Consider a more dynamic source
   If automation is your long-term goal, switching to a data source like SQL Server, Dataverse, or even pushing data into a Power BI Streaming dataset could give you more flexibility. These options allow near real-time or scheduled updates without republishing.

Unfortunately, Power BI Import mode doesn’t support write-back or appending data automatically from Excel.

[v-sgandrathi]

Hi @Susan513,

Thank you For using Microsoft Community Forum. Thank you @lbendlin for your response which is correct.

Power BI is designed primarily as a read-only reporting and analytics tool, and it does not support row-level update operations similar to those performed using Power Automate or Excel VBA. Specifically, Power BI's data model uses a partition-based refresh mechanism, meaning it flushes and reloads data rather than updating individual rows.

To track SPI trends and reflect the latest data, use Power Automate to manage your Excel file, add new rows for new dates and update SPI for existing ones. Then connect Power BI to that file and enable scheduled refresh. This keeps your reports up to date automatically without needing row-level updates in Power BI.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Regards,
Sahasra.

[Susan513]

Thank you. The issue I am trying to resolve, we have to refresh Power BI to get the updated information. The automation updates the excel with the new information, then we have to refresh the power bi again to have the trend updated. So for each change, 2 refreshes. It works fine, just so many refreshes - I worry about sustaining this long term.

[lbendlin · Accepted solution]

Create another flow in Power Automate that triggers when the excel file is changed. Include  a five minute wait. If during these five minutes there was another change, cancel the flow. If not, initiate a semantic model refresh.

That way your Power BI stays up to date and you minimize the number of required refreshes.

If you want better than that you need to switch from Excel to a Direct Query capable data source.

[lbendlin]

No, Power BI has no "update"  concept. It only supports partition level flush and fill.