XML values instead of Labels in Power Query table

[kiryakos]

Dear Colleagues,

I am facing an issue similar to the topic “XML Values in Table are displayed” posted a month ago.

I try to collect my data with power query, but some results appear in xml values instead of labels. Take into consideration that I have reset the table data as suggested by @kal_lam but the issue persists. I also tried to delete some entries (results) and the power query keeps showing the initial table (current + deleted results). When downloading my data in excel the dataset seems fine (only current results with labels instead of xml values). The issue appears when using power query.

Please advise.

Best,

[Kal_Lam]

Hi @kiryakos,

Could you kindly list down the exact steps you followed and also share with us the screenshot of the same so that we could also replicate it at our ends.

[kiryakos]

Dear @Kal_Lam,

I created a project with and then I changed the XML values from “automatic” to numbers (1, 2…), which if I am not mistaken were later changed automatically back to this

I redeployed the project to it’s final version, submitted a couple of forms and then used the instructions to get my data using power query. This worked well apart from some results which appeared with XML values

I followed your instructions reseting my data but this didn’t solve the issue.

I then deleted four of the five entries from the online table to see if this would affect the results appearing on the query but nothing happened (EDIT: today the results were refreshed in the query succesfully, but XML values still remain there)

As a desperate solution I made a clone of the project and created a fresh excel to get the new data with power query. Unfortuntately, the results also inlude XML values instead of labels

I also reset the results of the clone but the issue persists.

Best

[Kal_Lam]

Would you mind trying the approach as outlined in the post discussed previously (it should solve your issue):

API to GET Kobo submissions using 'label' rather than value

After making your data public: Settings–> Sharing–> [image] USE THIS GENERAL STRUCTURE: Default Language: https://kc.humanitarianresponse.info/“USER_ACCOUNT”/reports/“FORM_ID”/export.csv Or other instance… EXAMPLE of FORM_ID_IN_KOBOTOOLBOX: a5n9jp9qxyiKPfRNYq7KAt If “english” is one of your languages… https://kc.humanitarianresponse.info/ USERNAME /reports/ FORM_ID_IN_KOBOTOOLBOX /export.csv ?lang=english

[kiryakos]

thank you for the reply,

I followed the instructions and downloaded a csv file, which includes the data in labels as expected. I also used the link to feed it to power query, but I am afraid it only receives one tab and not the extra ones for the repeat questions. Unless I am doing something wrong.

Best,

[Kal_Lam]

If you wish to work with the repeat groups, here is a post discussed previously (it should help you solve your issue):

Pulling data into Power Bi via API: Skipped questions at set up not added when answered post set up

Hi For capturing baseline data and recurrent updates, we have created a questionnaire (xlsform) which has become quite complex by now. There is a lot of skip logic and repeats – for instance, capturing either data on a school or data on a ECD centre, as well as asking for the number of preprimary classes and then for enrolment numbers of each of them. Janna’s wonderful youtube tutorial (https://www.youtube.com/watch?v=mDokTnjM6Fo&t=518s) worked perfectly to get the data from kf.kobotoolbox.org …

[kiryakos]

Dear @Kal_Lam

I am afraid that even though this thread and video helped a lot in my general understanding of the API and how to pull data, they don’t solve the issue I am facing.

In particular, even though I am now pulling data using json as instructed in the video, the data still appears with XML values instead of labels.

Best,

[Kal_Lam]

It seems like you have multiple deployments and you changed some value codes with the later deployment. Did this happen?

[kiryakos]

Yes, indeed.

[Kal_Lam]

If this is the case, it should be a bug as outlined in the post discussed previously:

XML Values in Table are displayed

@wroos, I have created a GitHub issue. You could follow the same here:

Also please be informed that i have created a GitHub issue for the same.