Append new data to the existing one instead of overwriting

Hello,

I am new to power bi and have question about the data refresh.
Is there any way that when Power Bi refresh the data, it appends to the existing data instead of overwriting?

I am trying to create a power bi report for inventory analysis. So, need to keep track of how much quantities of the items were available at a particular date/time.

Currently, the inventory table is getting the current data (how much quantity of an item is available right now.) and when it refreshes, it overwrites the existing data.

For example, if the user is looking at the report today, should also be able to see what we had in the inventory last week, or month etc…

Thank you very much in advance!

@gkhokher What is the source of your data? Are you refreshing it manually or on the service?

@datazoe , I am getting the data from database server. it will be an automatic scheduled refresh.

I’m not sure how to achieve this in Power BI itself. A couple workaround ideas though, but depends on how many lines/rows you inventory table has.

  1. If you’re not going back too far in time then your DBA might be able to write something to snapshot the inventory data daily. Then you’d get say 30 tables for last 30 days which you could date stamp in power query and append the tables either in PQ or with a UNION Dax table. (bit clunky though and not much history)

  2. SImilar to 1, you could bring the data to Excel daily with a query to the database. Then set a column up in worksheet for date downloaded and append each day onto another excel tab, using this as your data source for PBI.(but again does require manual intervention so maybe not ideal. Possibly your DBA could replicate this in the database).

  3. Better option might be access the sales and purchases transaction tables and derive an inventory amount from the quantities/costs/sales bought and sold. Plenty of SUMX going on here. The derived amounts could then be controlled/proved against the inventory table you already have.

Pete

@BINavPete , Are there any options in power bi to achieve this? May be export existing data to excel before refreshing the cube?

Hi @gkhokher

You can copy and paste table data either from the Power Query/Transform data section or from the Power BI section once the data is loaded.

My suggestion would be to copy to Excel from the Power Query/Transform data section and disable loading it to PBI. This will keep you PBIX data model tidy. You do this by right clicking the query on the left hand side and unticking Enable Load.

To copy the table data to Excel click on the top left hand corner of the table and you will see the option to Copy Entire Table. Paste this into Excel underneath your previous days’ data and add a date column.

Then upload to PBI from this sheet. The only downside here is the necessity to refresh the data twice.
It might be cleaner to run the initial refresh into an excel tab (Transform data also exists in excel under the option Get and Transform Data on the data tab).

Hope this helps
Pete

HI @BINavPete ,

Is there any way to do these steps automatically instead of the user doing it manually?

Thank you!

Are you planning on refreshing this once a day, say in the evening?

If your data’s in SQL, would it be an option to snapshot your data each night into a table with an inserted date column added via a scheduled stored procedure, and use this as your dataset in PowerBI?

Hi @gkhokher

Depends where data is stored. If you’re going down the excel route,
ie daily refresh tab | Cumulative data tab then automation is a bit tricky. Could be done with some bat files and VBA in the spready with a bit of Power query, but bit messy.

Better option was suggested by me in first response and follows @jamie.bryan’s idea.
Best way is for automation in SQL Server by appending the snapshot onto a table that will refresh directly to PBI. This can all be timed in SQL agent and in PBI Service via online gateway. It is how we are managing a lot of our dataflows in house

Pete

Hi @gkhokher, did the response provided by @BINavPete help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

@BINavPete , @jamie.bryan ,

Thank you very much for the guidance. I will try the SQL snapshot option and get back to you.

Thanks Again!

Hi @gkhokher, a gentle follow up if you were able to resolve your question by the solution provided?

Hi @EnterpriseDNA , I am still in the process of implementing the snapshot option. I think we can consider it resolved as now we have the idea of what needs to be done.

Thank you for the support!!

I have the same issue. The solution provided seems cumbersome. Is there no way in Power BI to take a snapshot of the data each day and save it inside of Power BI? I am looking for an automated solution to this problem. I would like to be able to see my historical inventory levels for any day in time.

Thanks

I don’t think that is possible, you will either need a way of getting the Excel table to be replicated in the same location or get a snapshot created in SQL.

I wish this was possible as I would’ve doing this ages ago :joy:

Happy to stand corrected if I’m wrong.