Deleted data from the server still popping up Excel Power Query

Hi mohan_n

I am going through literally the same problem as we speak - I am testing pulling data from KoboToolbox directly to Excel via PowerQuery and though I succeeded to pull the data, once I deleted it from Kobo, it would still appear on Excel. I tried a number of things, including creating a new spreadsheet to pull the data once more and it would still pull old/deleted data. I’ve been refreshing for the past 3 days to no avail. I also tried to make the forms private then public again before pulling, but it still didn’t work. Would anyone be able to let me know if there are certain steps that can help solve this issue much faster? I unfortunately have to hand over my work to my client and I simply cannot give them results with dummy data still appearing.

Much appreciated!
T.

Welcome to the community, @Tonic! Would you mind trying this API discussed previously (it should solve your issue):

@Kal_Lam I think that a lot of people is figthing with this…it is simpler to get the data in a “Machine readable name”( name) than a “human readable way” (Labels). Also you have to make your data public ( not suitable for everyone)
We should be able to query API for labels in different languages in a private way…Please, pass forward this to the devp. team:-)
Regards

1 Like

Thank you @nca_hum_div, for bringing this to our attention. @Josh pinging you with this post on what @nca_hum_div shared.

Thank you both for your input. My concern was not necessarily with accessing the “label”; I instead was happy to retrieve the “value” which I was receiving when using the data API as before. My concern was when I delete data from KoboToolbox (entries disappearing from the table), those entries would still appear on my linked excel spreadsheet. I was wondering if there was a way to ensure that they stop appearing on my spreadsheet. Regular refreshing has not helped (still viewing data 4 days after deleting them). I was wondering if there was an issue with them being 100% deleted from KoboToolbox - maybe there’s something there that can be explored and that’s why they still appear on Excel?

Further, I second @nca_hum_div 's concerns about making data public - this brings up data privacy concerns and I’m sure not everyone would be happy to have their data accessible as such.

Hi @Tonic if you manually delete the dummy data from the spreadsheet and then pull from kobo does the deleted data still reappear? If you are unable to see the data you deleted at the API endpoint, Excel will not be pulling it in.

1 Like

Hi @nca_hum_div you are able to access the labels if the form is shared but not the data. You can see the various translated labels at the endpoint:

curl -X 'https://[kpi]/api/v2/assets/{asset_uid}/?format=json'

The translated labels are nested as follows: “content” -> “choices” -> “label”

1 Like

Hi @Josh I have tried deleting data directly from Excel - once I do that, and I press refresh, it doesn’t appear again. However, once I fill in a new form and try to retrieve new data onto my spreadsheet, data does not appear - I would need to undo that column deletion before new data appears. I did try something new: when attempting to add in the link to web from excel, I would include the URL without “.xls” (therefore looking like this: “https://kc.humanitarianresponse.info/api/v1/data/680427” instead of "“https://kc.humanitarianresponse.info/api/v1/data/680427.xls”) and that helped in retrieving only available data (therefore deleted data did not appear). Yet, the problem now is that what appears on my PowerQuery is data only and questions that were in the form of notes do not download (since there no data points to be collected). While I’m happy that only new data is downloaded, I would have still preferred to download the note fields.

1 Like

Hi @Tonic, I’m not sure I understand the new problem — can you please clarify why you are needing the note-type questions to be included in the data.

1 Like

My bad - I am creating a 3-way link whereby I extract data from Kobo to one spreadsheet and then link up the latter to another sheet (that’s already loaded with macros). I needed data to download in the same format as I had set up the form, in order to pull all data without interruptions. I can still make it work without PowerQuery pulling in the note-type questions, but it’ll simply take me more time to organize. I’ll still try to add on dummy data and delete, then see if excel would be faster to update this time. Thanks for your help on this!

2 Likes

So initially retrieving data from “https://kc.humanitarianresponse.info/api/v1/data/680427” did work fine, however it was not loading data from new forms. So that is not a solution.