Add New Records to Static Table

a68tbird

New Member
Joined
Nov 15, 2011
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hello All -
Hope I can explain my situation thoroughly here. I have a table of products that I have modelled and loaded to my worksheet through PowerQuery. Source is SQL Azure db. I have a calculation based on the retail price of the product called "Royalty". Once this Royalty has been calculated, it has to stay at that value regardless if the retail price changes. Essentially, the table has to remain static from now on. So, I disabled any automatic refreshing, so there's no chance the values could accidentally get updated. However, there has to be a way to accommodate new products added to the database. So, I created a new query that is a duplicate of the original query, however I filter for products created since the file was last saved, and append that to the original query. My issue now is, how make these new products a part of the static table? If I run the query today it picks up a couple of new products, and I save the file. If I run it next week, I update the created date, and it means those new products from last week won't be picked up. Essentially, new records need to be added to a table, which then becomes the source for new records to be appended to.

Anyone have any thoughts on how to go about something like this?

Thanks!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
There is no persistent write ability currently in PQ, unfortunately. All I can suggest is you cut and paste values the results into a spreadsheet and load from there.
 
Upvote 0
I believe I figured out a workaround. My final output table, we'll call it "AllProducts", I use as the source for a new query, which I'll call "Source". I then change the data source of the "AllProducts" query to be "Source" thereby creating a loop. New products get inserted into the "AllProducts" table, outputs an updated table which is then used as the source for the table when the query is run again. Will run some more thorough testing, but so far, this seems to work. It's essentially what you have suggested Matt, except I don't have to cut and paste anything.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top