How to connect excel sheet to get only new submission

[Amriddin Karakhonov]

Hello!

I used Excel Power Query to pull data from KoboToolbox. I did data cleaning in the same file and when refreshed to get new submission my cleansed data refreshed as well. Could you please, suggest an easy and best way to tackle this issue. What I tried so far but not succeed. 

1. In a new excel file from Data ribbon Existing connection pulled data but again when refreshed lost cleaned data
2. In a new excel file Data ribbon New Query pulled data cleansed but lost after refresh...
3. I wanted to have pull and work in the same sheet apparently it doesn't work in this way...
4. Maybe I need a pulling data and work table data but how to get only new submission by using Power Query?
5. What would you suggest? 

Many thanks, 

Amriddin

[#2]

Hi Amriddin,

Power Query is designed so that clicking Refresh goes back to the source file and pulls in all of the data and replaces any existing loaded data.

You may want to consider copying and pasting (as values) the loaded cleansed data into another table after each refresh in order to keep the history.

[#3]

Thanks Wyn! Yes you are right, but i want to have this process automatic other wise i will go crazy copy/pasting after each cleaning into another table. It seems VBA skills needs to be applied for this no (unfortunately i don't have that skill)? Or is there any possibility to find out new submissions while refreshing in Power Query and if there are new submission then merge it working table....

[#4]

Yes VBA would definitely be the best route.

It is possible to do a self referencing query in 2 stages but I could imagine it might be difficult to manage.

See attached