OData link in excel is missing data

Support
1

1. What is the problem? Be very detailed.
Using the OData link in Excel I'm able to get data from some fields but nothing from within a group. The group is not a repeat, it is there simply for organisation of the form.

2. What app or server are you using and on what device and operating system? Include version numbers.
I'm using ODK collect 1.27.3 and ODK central 1.0. My excel installation is on Windows 10 and is MS 365

3. What you have you tried to fix the problem?
In the OData connector (data preview) in Excel I'm able to see the missing group which is called "weigh" and it has the word "record" in the cell. Does this mean that OData cannot work with groups? This is what the OData preview looks like in Excel:

image

When I download the data directly (in zip format) there is only one .csv file inside as would be expected for a form without repeat groups.

I have uploaded a new form definition with the group removed and this deletes any previous data entries (the records are still there but values that were within the group are lost). However, new form submissions with these fields outside of the group are now visible when using OData. Personally I find groups incredibly useful for organising forms (grouping related questions on a page for example) rather than having to use the one question per swipe model.

4. What steps can we take to reproduce the problem?
Create an XLSForm with a group defined. Collect responses. See if OData link shows responses to questions contained in the group.

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
Although I have "solved" this by removing groups from my form is this a short coming of the OData standard? I think it is fully understandable that repeating groups would cause difficulty but non-repeating groups would be a very useful inclusion.

2

Would you be able to share the form definition here as well? Ideally the original one containing the group.

3

Groups are supported! Once you've configured the URL and the credentials for your OData feed, there's a "Transform Data" button next to "Load". You can use that to select which groups to expand. You'll see an expansion icon next to each column header that represents a Record:
Screen Shot 2020-09-19 at 2.07.00 PM

Clicking on that icon will let you select which columns you want to expand out and whether you want them prefixed by the group name or not.

If you've already added a data source, you can click on Data > Queries & Connections and then right click on your query. Then clicking on Edit will get you to the same editor where you can expand and collapse columns.

This could certainly become tedious for large forms where all of the data is required in a single sheet. Does anyone know of a way to expand all columns of type Record? I see it's scriptable but I'm surprised there's not a button for it.

4

@LN I can confirm that the expansion option on the OData Feed with a not-so-large form (80 questions & 700 submissions and 7 groups), becomes an issue for Power BI / Excel, and the system times out after a while. The number of rows that are generated by each group grows exponentially and turns out unmanageable for the system. An ideal scenario would be that the OData feed retrieves the information like the CSV default download.