Query to get data from worksheet that is deleted daily and retain it elsewhere

[bazbuyer]

Hi I previously may have posted this in the wrong forum. I have a source data sheet S and destination sheet D in another workbook. I've run a query (excel 2016) to get the data from S to D and using append if I change a value in S it refreshes and changes in D which is fine. However, I want to delete the data completely in S on a daily basis and put new data in S (usually on a row or 2 per day) and then update the new data to D without deleting the previous. So S is a totally new database each day, D grows day by day and maintains all the previous data. Is it possible to do this or am I missing something. Currently if I delete S it also deletes D to match, if I add to S without deleting then it adds to D - I need this for a later stage but not this one thanks

 

[Ron Coderre]

I have a fairly robust model for handling this situation, but these are the basic steps:
1) Create a PQ query in the Dest wkbk to read the Source data... eg SrcQuery
2) Reference SrcQuery and name the new query: AllData
2) Load the AllData results into a sheet in the Dest wkbk...Table will be named AllData
3) Change the AllData table name to tblData
4) Add that table to PQ (Power Query.From_Table)
5) Add an append data step to AllData...Appending tblData to it.
...Almost done

In the workbook..the tblData will probably have duplicated data
- Delete all rows from tblData
- Refresh the table (the current source will load)
- In PQ (or by any means) Edit the SrcQuery to retrieve new data
- Refresh the table again...the new data will be appended to the previous data

Is that something you can work with?

Future enhancements:
- Add a data identifier column to each load so you can delete a specific data source and replace it with better data (if the source for a day gets unexpectedly updated)

 

[sandy666]

@Ron Coderre
very nice but there is one thing: if I refresh table it will add data from the source but if I refresh it again, accidentally, it will add the same data again or if source will be empty it will add blank rows

 

[Ron Coderre]

Those steps were just to get you on the right track.
There are several approaches you could take. Here are two:
- Merge the new source data to the destination data and only keep rows NOT in the destination. THEN append.
or
- Purge new rows from the destination data (in case, the new data is more current)

 

[sandy666]

Those steps were just to get you on the right track.

sure, thanks I'll try again

 

[bazbuyer]

Thanks fo

I have a fairly robust model for handling this situation, but these are the basic steps:
1) Create a PQ query in the Dest wkbk to read the Source data... eg SrcQuery
2) Reference SrcQuery and name the new query: AllData
2) Load the AllData results into a sheet in the Dest wkbk...Table will be named AllData
3) Change the AllData table name to tblData
4) Add that table to PQ (Power Query.From_Table)
5) Add an append data step to AllData...Appending tblData to it.
...Almost done

In the workbook..the tblData will probably have duplicated data
- Delete all rows from tblData
- Refresh the table (the current source will load)
- In PQ (or by any means) Edit the SrcQuery to retrieve new data
- Refresh the table again...the new data will be appended to the previous data

Is that something you can work with?

Future enhancements:
- Add a data identifier column to each load so you can delete a specific data source and replace it with better data (if the source for a day gets unexpectedly updated)

r your reply

 

[bazbuyer]

Many thanks Ron and Sandy for your input