I have created a form having repeated section and trying to access the data in Power BI. Whenever there is an empty field value inside repeated section I am getting below error in power BI
"The Key matched more than one row in the table"
This is snippet from my form design:
I am getting the same error for other fields (that are in repeat section) if they don't have any value.
I am using ODK collect v1.29.3 on Android device and ODK Central v1.1.1.
I tried default value and dynamic default but it didn't work. My idea was to have some kind of value populated even if a field is not visible.
Is this a known issue and is there any solution?
This sounds like more of a PowerBI issue than an ODK issue.
You might get a better answer if you post it on a PowerBI forum and indicate whether you get the error while loading the data sources or joining the data sources or while creating the charts.
The solution might mean a change in how you load the data into PowerBI rather than how your ODK form is designed.
I agree with Noel. It looks like there is a problem when defining the in powerbi to link both (the main data sheet and the sheet with the repeat data). Something that may be easy to test for you could be to try to get the xls/csv files with your data and try to do the same thing in PowerBi. If you can make it work that way it would be an indication that the issue is not with the design of ODK and it could be with the connection between PowerBI and the ODK server?
Thank you @noel_cartong and @arturo_garcia for the response. I'll do some more test and probably use csv file as data source instead of direct odata connection. I'll also take this issue over to the Power BI forum and see if I can get away with the issue.
I agree with @arturo_garcia another alternative test you can run would be to transform the data on Power BI when connecting to ODK and insert your own column with unique primary key. Repeat group have the same uuid whih could be creating the conflict.