I am connecting excel to ODK central server, and it is displaying a few variables in excel. among 115 it is displaying only 6variables.
How to handle this issue?.
How many submissions are in your form and are you using survey logic such as relevant in your survey? If there is no data for the variables yet, they might not appear. Similar to how Power BI works as described in Some columns are missing when data is imported to powerBI using OData - #2 by danbjoseph
Thank you for your support, every thing is fine now
Is it possible to display the labels instead of label values(I mean if variable sex is coded as 1 for Female and 2 for male) then now in excel there are values(1 and 2) instead of Female and Male.
Any method to have this labels(Female and Male)?
Can you change your form to return the labels you want instead of 1, 2?
If not, have you used Power Query before to transform the data before it's placed in an excel table?
If you give me an OData link to one of your forms, or an OData link to an example form I will give you the powerquery code that will transform a few fields, reorganise columns, remove other columns etc and explain how to make similar transforms.
Remember that 1 and 2, are the best option for coding variable in SPSS or STATA. I was just asking if in query editor in excel there is a way to transform these label values into labels.
If you have any image or video can help.
Thank you!
You could use power query to create a new column that duplicates your 1,2 column and then replaces those values with readable ones.
You could also do a calculation in the form itself to return another field based on the input
Edit:
Copy the OData link from Central from 'Analyze via OData'
In Excel, Get Data->From Other Sources->From OData Feed
Paste the OData .svc link
Select the 'Submissions' table and Transform Data
Then you can;
a) Right click the column of interest and Replace Values, for as many times as you have values, then your PowerQuery code will look like this afterwards, this is only good if there are a very small number of values to replace:
let
Source = OData.Feed("https://YOURSERVER/v1/projects/1/forms/ODK_PowerQuery.svc", null, [Implementation="2.0"]),
Submissions_table = Source{[Name="Submissions",Signature="table"]}[Data],
#"Replaced Value" = Table.ReplaceValue(Submissions_table,"1","blue",Replacer.ReplaceText,{"colour_pick"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","green",Replacer.ReplaceText,{"colour_pick"})
in
#"Replaced Value1"
b) But you need the integers for SPSS, so to keep that column and have a readable one, you can add a conditional custom column (Add Column, Conditional Column) calculated from the numeric column, which will then look like this:
let
Source = OData.Feed("https://YOURSERVER/v1/projects/1/forms/ODK_PowerQuery.svc", null, [Implementation="2.0"]),
Submissions_table = Source{[Name="Submissions",Signature="table"]}[Data],
#"Added Custom" = Table.AddColumn(Submissions_table, "Your_Added_Column_Name", each if [colour_pick] = "1" then "blue" else if [colour_pick] = "2" then "green" else null)
in
#"Added Custom"
Once you're happy with your data table (rename columns / rearrange them / remove any you don't want), Close and Load to a table or a data model and you have your data in Excel.
Or you can skip all of that and include a calculate in the form that returns another field with a readable label based on the selected value (See 'Calculated in the form' in the image above). This is an example with an if that has no error checking as it has a relevant on colour_pick being completed and there are only two options.
if(${colour_pick}=1,'blue','green')
But if you had more values, you could use an instance lookup, that should look like this but I've made a mistake and can't for the life of me see what I've done wrong! 
I have them working elsewhere with a 6000 entry choice list.
instance('colour')/root/item[name=${colour_pick}]/Readable
This is the form with the if that I also fed the submissions to Power Query:
ODK_2_XL.xlsx (23.8 KB)
1 Like
Great!
This is very supportive
How to splint the multiple responses in Excel when your excel is connected to excel or Power BI?
Do you mean where you have a select_multiple and the field contains 'choicea choicec choiceb'?
You can split a column by delimter as part of your query, then rename the columns as you need
Note that the values are ordered in the order they were selected, so it could show 'choicec choicea' or 'choicea choicec'