Chalenges in refresing kobo data linked to excel after amending the tool

I have linked my kobo form directly to the excel and developed dashboard such that I can always refresh to get most updated data. However, when I amend the tool, it refuses to refresh, hence not updating the dashboard. What can I do to ensure that the dashboard is refreshed even I f I make changes on my kobo form?

Hi, can you elaborate on more information? How did you import data to excel? By Power Query? If it is power Query there is a simple solution.

1 Like

yes, I did that using power Query. Please share with me the simple solution and if it can still sustain refreshing the data…dashboard even if I make changes in the tool?

Hi again paul,

Sorry for my late response. It’s beening hard times.

You can still sustain refreshing your data, and you need to arrange the your data though “Advance editor” in the Power Query. I believe you edited a question or deleted a question in Kobo and as a result you get this error. You can find the steps below to fix the refreshing issue. If you can’t solve it just share a screen shot so I can explain better or make a video to show it(I currently don’t have time for it but if needed I can do it as soon as possible).

1- Open the Power Query editor
2- You should see a yellow ribbon indicating your data has errors. You can find the error in the yellow ribbon.
3- On the right corner of the ribbon, there is a go to error button. If you click it, it will take you a specific step on the right corner in the Steps lists.
4- According the error given by the yellow ribbon, you need to find the column name in the “Advance editor” and delete it from the the steps.

1 Like

@paul_maleya, maybe this article should also be helpful for you:

Hello Osman,
Sorry for the late response.

I have done it and it has worked! Thanks so much.

And what if I want the question that I have added to be part of the data I want to analyse? Instead of deleting it what should I do?

1 Like

Hi Paul,

I am glad it worked,

For your second question, It may appear in your data set automattically, but if it doesn’t appear automattically. Go to Query editor again, choose your data, and on the left side in applied step, choose the first step, after you click there should be a gear button next to it. Click on it and it will open a window and your will see the question chosen. Go thought the, you will find your new questions unchecked, check and apply. They will appear in your data set after this change.

If you have more questions, let me know

1 Like

Hello Osman, I keep on trying to follow the steps you have provided and let you know if it will work
I have another quagmire though, I decided to do another excel to accomodate the question that I added, unfortunately the previous submissions dont have the answers for the newly added question.(The new question is categorisation of counties either ‘A’ or B’‘). When I try and replace null with the correct categorisation( ‘A’ or ‘B’), I must start with categorising first set of Counties "A’, then categorise ‘B’ counties. Unfortunately, the power querry Applied steps allows to save the last step which I am now promted to save and load. What I can do so that I can update the responses for the previous submissions such that all the counties are well categorised as A or B? and able to load all the counties in excel and do cumulative analysis? Thank you for your continued assistance

Hi Paul,

Correct me if i am wrong,

So you have a dataset where you have countries and a group name for contries, such us UK, Group A? And some of the previous submissions have country but not the group? And you want to add group to this missing values?

1 Like

Hello Osman,

Yes absolutely. I want to add the grouping for initial submissions.(It is counties/districts). And to retain the changes as well when I refresh the excel dataset which is linked to the kobocollect

I believe you can use conditional column, have you tried that?

Create new conditional column, all old submissions will have the grouping you want. After that you can use “if-else” sentance to merge the two columns. Lets assume the orginal column is A, and new column is B. So you will write annif sentence, if A is empty use B, else A. You can find the if sentance in DAX on internet. You will have column “C” which will be combination A and B and you can use it

1 Like

Hello Osman,

I tried the conditional column and it has worked really nicely!!! Thanks alot for the your support!


@osmanburcu, @paul_maleya :clap: :heart: :partying_face: