Add Dynamic Data Daily to a Static Table

A daily email is sent to the warehouse managers each day that includes the inventory value as of that day. I’ve been adding these numbers to a spreadsheet so we can do some other calculations, but I’d like to find a way for Power BI to add this data to the history table. Right now the history table is an Excel spreadsheet and it might be best to write to the Excel file so future updates to the report don’t overwrite the historical data.

Attached is a pbix file with sample data and an overview of my objectives. Also attached is the underlying Excel file. Thanks for any help the community can provide.

Adding Rows to Historical Inventory Table.pbix (338.5 KB)
Inventory History.xlsx (308.7 KB)

Bumping this post for more visibility from our experts and users.

Hi @npeterson - For making it dynamic, you can use Power Automate. This will allow data to be extracted from Power BI and loaded into static Excel file dynamically. There are examples available online on how to setup the flow.

This doesn’t require premium connectors.

Ankit J

Hello @npeterson

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

Thanks @ankit. Do you know if there are any examples of this on the eDNA site? Or can you point me to an online example? Thanks.

Hi @npeterson - Please refer to these videos from curbal. There are similar videos available. This shows how to extract data from Power BI using Power Automate and export to excel in sharepoint.

For your case, you will need modification to append to excel. Also, instead of a button, the flow can be scheduled also by pointing to a published Dataset in service.

Ankit J

Thanks @ankit. The videos were very helpful to get me started, but I’m still missing something. I can only make this work if I set a manual trigger from within the report. When I try to create this to trigger (based on a set time) from the workspace, it fails. And the message that comes along with that is frustrating. The Status is “Failed” and there isn’t any indication (that I can find) that explains what went wrong. I’ll keep trying.