Missing Data when loading Data into PowerBi

I recently linked PowerBi to KoboCollect so that data is loaded unto my dashboards by clicking a refresh button in PoweBI. However, I reployed my survey forms. PowerBi can only read data from current form and not the previous forms yet data is stored in the same server. I have checked the box to allow data from previous versions to be dowloaded but it doesnt make any difference. Is there any other way around it?

Hi,
Could you kindly provide the following additional information:

  1. Is it that all data collected before the edit of the current form cannot be seen? or
  2. Is it that variables/questions that were only in the old form and not the current form are the ones which cannot be seen?

Stephane

Hi Stephane,
All the data can be seen in the KoboCollect server but when I download to PowerBI, only the current form variables and data is loaded but the data collected before editing the old form cannot be loaded to PowerBi but I can view them in the server. I am forced to edit the form and include the value in the empty cell in the new form for data collected before. See what I am talking about.

Thanks,

Alex.

Hi
Can you send me a link to your project on inbox so that I look at it?

Stephane

Hi Alex,

I have the same problem as well. All 246 rows of data from Kobo have loaded on to Power BI, but several rows have null values. This wasn’t the case when I was exporting data from Kobo and linking the xls to Power BI. But I started using the Kobo API and am now noticing these null values.

Were you able to resolve your issue?

Thanks,
Shreeya

Hi @SNB
Could you provide step by step screenshots of your linkage (always cover the identifiers).

Stephane

Sure!

I went on this link and entered my username and password: https://kc.kobotoolbox.org/api/v1/data.csv

I selected the correct instance from this csv

image

I changed the .csv to .xls. I checked the download using the url and noticed null values. I did an export from Kobo and looked at the same rows but didn’t see any null values there. So my guess is the problem lies in these steps and not in Power BI.

Then I went on to Power BI

image

All 246 rows of data load successfully. But the columns highlighted below are all required columns that should not have null values. In the direct Kobo export, all of the null cells have values

Would appreciate any insights!

Hi
Thanks so much for the details provided. I would like to ask that you do the following,

  1. Could you provide a comparison of the values presented as a null vis a vis the the values you see when you download.
  2. Check whether the nulls are limited to responses from specific users or they are sporadic.

The problem seems to be in the conversion from .csv to xls which could be caused by the way excel converts csv to excel. That is why I want to confirm the records for the two requested in point 1 above. It could be a delimiter issue among many others.

Stephane

Thanks for the suggestion, Stephane!

Upon closer inspection of the other columns, it looks like the only two columns with missing values are V/Y/region and V/Y/school. In the screenshot below, the rows on top are from the Kobo API url and the ones at the bottom (with the values) are the corresponding, direct Kobo exports.

image

I looked at the users who entered this data and there are some repeats, but it’s across the board. It also doesn’t belong to submissions from a single date/week. The only other thing I can think of is this form had several deployed versions. Could that create an issue in terms of conversion?

I also tried instructions on this page to see whether connecting through csv instead of xls could resolve the issue, but the csv also has the same null values.

Hi
When I look at the export with the null, I see a null through out the row referring to this image.

On your question about multiple deployed versions, the API will only get the data in the format of the most recent deployed version. This should not pause as a problem unless you changed some type e.g. If something was text and it was then converted to integer type, when this is processed, the text may be deleted since they don’t meet the definition of the integer/numbers type.

Stephane

1 Like

Hi Stephen and SNB,
I figured out the problem was the multiple deployments. Stephen as you rightly mentioned it could be an issue of deployment. I had to fix it manually which was a pain process. Luckily the cases were fewer.

1 Like

The question then is that how do you ensure that the older version data and the new version data are downloaded into PowerBi? What I noticed is that the new variables (changes) are captured in different columns.

Target for MODEL FARMERS Training Target for Demo Plots Establishment
30 30
30 30
27 27

As you can see after fixing it manually, I have two columns with the same data. This is to cheat the system somewhat so that when I download, I get the data in the column that us read by PowerBi. Not an efficient method but it worked since my Managers were on my neck for a report :slight_smile:

Hi Stephane,

Thanks again for responding.

Yes, there are null values in the Power BI screenshot almost in every row. I just picked out a few rows in my excel spreadsheet screenshot to illustrate what the data looks like when I use the API vs the export function in Kobo directly. In the Kobo export, every single one of these rows is populated because this is a required question.

I’m not sure I follow your proposed solution. Would appreciate it if you could expand upon your suggestion.

Shreeya

Hi Shreeya,
I have had a look at what @AlexRutto77 had indicated?

On your question Alex, the

You just ensure you get your form very neatly done to avoid such problems. My approach has always been to create a new project with the very final version of my form and as such make no further changes to the questionnaire.

Stephane

2 Likes

Thanks Stephane and @AlexRutto77. Managing multiple deployments better seems like the way to go!

3 Likes

I am experiencing the same problem only this time complete columns are missing from the Power BI Sheets. I have not redeployed the project and as it was earlier put, the data is intact once I download it.

Hi @ROboKo,

Welcome back to the community! Would you mind sharing with the community a screenshot of the issue you are facing. This would help the community to understand your issue more clearly.

Have a great day!

1 Like