Dear all,
Since I’m new to the Power BI community I think it is appropriate to start with a brief introduction. My name is Niels and I work for an international development organisation. The project I’m currently working on is situated in Ethiopia. An important tool in this project is a household survey that will be (digitally) taken at 94.000 households. The goal of this survey is to get a better understanding of the priorities that citizens have in the 5 basic service sectors (education, health, WASH, agriculture, and rural roads).
The surveyors will be using a tablet with KoBoToolbox (surveying app, very similar to the well known ODK) to collect responses digitally. After uploading all responses are stored in a central cloud-based environment. There is an API available so pulling the data in Power BI works very smoothly. (https://support.kobotoolbox.org/pulling_data_into_powerbi.html) So far so good!
The issue I’m struggling with is that the survey data I get from KoBoToolbox is very ‘flat and wide’. This makes it hard to create reports based on the collected data. Every response represents an individual row in the spreadsheet. For every question answer there is a separate column. On top of that, select many questions are split out in one column per answer option (where the value is either 0 or 1)
The above results in about 540 columns in total in my source data. The answers to the questions asked are all in the same row but spread over multiple columns. This is a very different data structure than the examples I find online or in the textbook I bought.
To be able to make visualisations I need to (un)pivot the question columns that belong together. This works fine but obviously only for one question at a time. So far, I have repeated the following steps in Power Query per question:
- Reference to the base query
- Delete all the columns I don’t need (I only keep the question columns and some general questions we would like to use as filters, for example: gender, age, education etc.)
- Unpivot the question columns
- Create the visual based on the new table
The above method works, but I do realise it might not be the best or cleanest method to solve this issue. Basically I'm creating one query per question. I’m writing this post to hopefully check with the experts if this is an acceptable method or that there are better ways to tackle this problem. Maybe there are even some people who have experience in transforming survey data. I’m curious to hear your thoughts and experiences!
I’ve also shared a example results file on my Google Drive that can be accessed via: https://docs.google.com/spreadsheets/d/1QEAtGLINDirajWs-dDH1UA5tOvDjwkbA/edit?usp=sharing&ouid=11339...
Many thanks in advance and kind regards,
Niels
