Add New Records to Static Table

[a68tbird]

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!

 

[Matt Allington]

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.

 

[a68tbird]

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.

 

[Matt Allington]

sounds interesting.