Power Query append data to existing table

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

@petesmith673 , 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 @petesmith673 ,

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 @petesmith673 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!

@petesmith673 , @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

Power Query append data to existing table

Happy to stand corrected if I’m wrong.

I have previously written about the powerful ETL (Extract, Transform and Load) functions that are now in Excel Office 365 desktop edition. We have seen how we can combine tables and unpivot data (Excel articles). In this article I will show you how to create a history table for imported data.

It is very common that you want to import data every day and keep it for future reference. It is easy to set up an import but unfortunately Excel always overwrites the old data with the current records. You might think that you could append data, after all there is a query type call append. However this does not append data to an existing table.

This problems has been discussed by Dirk Verliefden and Chris Dutch and others on Technet and I have built on their solution.

The trick is to create two tables

  1. A table bound to a query that gets the current data
  2. A table that appends the current data to itself

We cannot achieve this directly but can if we create a temporary table first.

Step 1: Create a table for your current data

Use Get and Transform Data on the Data Tab in the normal way.

Power Query append data to existing table

You must make sure it has a key column. I use one derived from the current data and time as shown below. My table is called “Table_prices” (top right).

Power Query append data to existing table

Step 2: Copy these data values into a new tab called “temp”

Power Query append data to existing table

Step 3: Turn this into a new table

Power Query append data to existing table

Rename this table (top right) to “table_temp”.

Step 4: Create a new query working against this table (this will be our final query)

Power Query append data to existing table

Doing this will create a new worksheet which will be our final history table. Rename it History_Worksheet.

Step 5: Edit the query to append the current data

At the moment this is normal query. We will edit it and convert it into an Append query Home > Combine > Append. In my case the data I download is in Table_prices.

Power Query append data to existing table

Save this query and view your worksheet History_Worksheet. It will now contain rows from both your original table and your temporary table.

Rename this new table “table_history”.

Step 6: Hack the query to use the history table instead of temp

Edit the query again. After the editor opens you click the Advanced Editor option (1) and look at the code (2). In line 2 it says that the Source for this table is “table_temp” and that this should be combined with the original data query, in my case “Table_prices” (line 4)

We replace “table_temp” in line 2 with the name of our new history table “table_history”. Now when this code runs it will use the history table as a data source instead of the temp table.

Power Query append data to existing table

Save.

We have now achieved our goal. Each time we refresh the table additional rows will be added from the current data table.

Step 7: Delete the “temp” worksheet.

The worksheet temp is no longer required and should be deleted.

Step 8: Tweak the columns

You can now tweak both queries to get the correct columns. Common tasks include adding additional keys for use in VLOOKUPs and removing blank or duplicate rows.

I hope you find this post useful. I welcome both praise and comments below – JB