Using append to grow a dataset

[steveJ]

I've been fiddling with desktop for a week or so now, and really impressed with it, i've one thing I can't seem to work my head around though.

I have a dataset consisting of 200 rows that I have pulled from excel, I have a query in desktop that pulls another 200 rows of the same data from a website but 'live'. So i've added a date in the excel file to indicate when it was from, and i've used formulea in the query editor to create a date stamp (each datetime.localnow())

I've then created a new table that i'm calling the 'master data' based on the excel data and appended the results from the web to it, and have ended up with a 400 row data table. I've then ceased the updating from the original excel data.

The behaviour that I am then expecting is that the next time I refresh the master data the new data (unique because of the new datatime stamp) from the web query would be appended to the master data, hence creating 600 rows. 

What actual happens is that the new data replaces the old data from the webquery and the datset remains at 400 rows. 

I suspect that there is some intermediate step that I am missing.

I realise that this could very quickly generate a lot of data, I only intend to be running this once a day once I get scheduling sorted out.

[Greg_Deckler · Accepted solution]

So, the behavior you are seeing is the expected behavior. Incremental data load does not exist quite yet. Therefore, the second query in your Append is replacing its data. Unfortunately, at this time you would have to copy your second query each day and add it to your Append query, disabling data load on your previous query. This is currenly the only real way to incrementally grow your data set.

[steveJ]

So I'm not missing out on something, it's just not ready yet. Guess It'll be back to macros in excel then. I suppose that in most corporate environments this 'warehousing' type of feature is dealt with elsewhere and then data can be presented to PowerBI for date filtering etc. 

[Greg_Deckler]

@steveJ- Something else that you could do would be to use an R Script step in your query to archive the information into a SQL database and build up an appended data set there. Then you could turn around and ingest this database table into your model. Kind of a convoluted way of doing it but probably no more so than Excel macros.

[steveJ]

I'd have to learn R and how to set up an SQL db at that point. A quick Macro should do it. Thanks! hopefully the functionality will exist, should be useful enough to warrant it.