Thank you for the clarification. The xls has 139/298 columns with a value in them. PowerBI continues to generate 87.
Also to note that column names also lead with the word group, where as none of the xls columns have the group name in them
Thank you for the clarification. The xls has 139/298 columns with a value in them. PowerBI continues to generate 87.
Also to note that column names also lead with the word group, where as none of the xls columns have the group name in them
Hello
The group prefix can be deactivated through your download setting.
@Kal_Lam Would it be possible to request functionality that would allow the user to specify if they want all columns to populate or not when querying kobo? Having a dynamically changing columns with an active survey does produce issues of its own with maintenance of dashboards tied to the kobo data.
@Kal_Lam To clarify, when querying Kobo from PowerBi, if there are optional parameters that could be passed to ensure all columns are generated within PowerBi.
The challenge is that unless I send in several test submissions that complete all of the possible fields within kobo, less than 50% of the fields will generate within powerbi. As additional submissions are created, that would then require additional ongoing maintenance to update the powerquery so that it accounts for the new submissions./columns.
To address the above challenge, would it be feasible to have an option where all the columns are displayed or the user can specify if only completed columns are to be displayed?
Hi @tom.joseph
I have gone through the communication trail above and I will narrow it to the last issue you raised as regards PowerBi.
This is an issue with PowerBi and how it pulls the data. As you rightly pointed out, you need to have data in all the columns to fully defined how you want those columns displayed. However, you could try adapting the fix done in advanced editor
I would also suggest you scheme through the following topics
Stephane
The workaround is:
@ks_1 Thank you for the detailed and step-by-step workaround. I will test this out but think it should resolve the issue. Appreciate everyone’s time identifying a solution!
Hi all, have been battling with this also and I think there are 3 parts to this business problem of setting up PowerBI integration before all the possible variations have been submitted - partly to do with the way KoBo behaves and partly to do with the way Power BI behaves. FYI I use JSON format when connecting to the KoBo API.
_id: 32356,
_notes: [ ],
_validation_status: { },
_uuid: "b1ea757b-a634-487d-a5f0-a5227661788a",
_tags: [ ],
rain: "no",
_submitted_by: null,
_xform_id_string: "af8Zwtsc6h9AW2UhH8qpnA",
breakfast: "Weetbix",
meta/instanceID: "uuid:b1ea757b-a634-487d-a5f0-a5227661788a",
formhub/uuid: "a56ee09be23b488696ab37b3fc7b4f5a",
end: "2021-03-25T23:48:09.211+13:00",
_submission_time: "2021-03-25T10:48:10",
_attachments: [ ],
start: "2021-03-25T23:48:02.617+13:00",
_geolocation: [
null,
null
],
_status: "submitted_via_web",
__version__: "vzmZ97h7GNEVEgko7yj2Qj"
}
This is something that can only be solved on the KoBo side - there is no way for Power BI to know about any data that isn’t exposed via the API! Possible ways to solve this business problem:
The thing is, when using the UI to Expand Results then you select the list of columns to expand - but not all are automatically loaded dynamically every time. You can manually go in after there are more submissions, and manually click the ‘load more’ button then select them all and save
If you look, you can see that when using the PowerBI UI to expand columns then all the column names are explicitly listed, so when new fields come through they will be ignored as they’re not on the list. e.g.:
However it is also possible to code the PowerBI side to be more extensible - this often requires editing in the Advanced Editor etc. - i.e. by editing the Powerquery/M Language rather than clicking via the UI.
For example, using a trick from this YouTube video we managed to set it up so our Powerquery handles new fields automagically (in this case data_repeat
is the column we want to expand to its fullest no matter how many underlying columns there are):
#"Dynamically Expanded data_repeat" = Table.ExpandRecordColumn(#"Replaced Value to handle nulls", "data_repeat", Record.FieldNames(Record.Combine(Table.Column(#"Replaced Value to handle nulls", "data_repeat")))),
We were using it in the context of repeating sections combining data from multiple unrelated repeats had to do a bit of extra jiggery pokery to handle nulls etc. - I’m sure the below could be done more elegantly but here is our Powerquery code if it’s useful to anyone:
#"Expanded data_repeat" = Table.ExpandListColumn(#"Renamed Columns", "data_repeat"),
#"Replaced Value to handle nulls" = Table.ReplaceValue(#"Expanded data_repeat",null,[null_record = "null_record"],Replacer.ReplaceValue,{"data_repeat"}),
#"Dynamically Expanded data_repeat" = Table.ExpandRecordColumn(#"Replaced Value to handle nulls", "data_repeat", Record.FieldNames(Record.Combine(Table.Column(#"Replaced Value to handle nulls", "data_repeat")))),
#"Handle scenario where no null records" = if Table.HasColumns(#"Dynamically Expanded data_repeat", {"null_record"}) then #"Dynamically Expanded data_repeat" else Table.Combine({#"Dynamically Expanded data_repeat", Table.FromRows({},{"null_record"})}),
#"Filtered Rows to remove repeats with nothing selected" = Table.SelectRows(#"Handle scenario where no null records", each ([null_record] = null)),
#"Removed null helper column" = Table.RemoveColumns(#"Filtered Rows to remove repeats with nothing selected",{"null_record"}),
Lastly,
Most of my issues have been to do with 1 and 2 above. We should consider putting something in the suggestion box about #1 as I know several colleagues have hit this stumbling block also.
Further to the comment above re extensibility, I also try to do things in PBI that don’t explicitly list all columns - just the ones I want to manipulate in that particular step. e.g.
etc.
Hello @nat ,
We are not using Power BI, but maybe an idea:
If there is an easy option to import xlsx into power builder. You could start with an xlsx KoBo download, even with the first submission, which gives ALL columns in the right order, and import this XLSX into Power BI to get the data set configured.
This won’t work easily because the steps to import data from an excel file and a JSON web source are quite different.
@wroos sorry for my slow response - agree starting with a list of all the column names could be useful - however in the case of JSON vs csv source in PBI, as the JSON needs to be expanded in order to access groups/repeats then there’d be a bit of manual work to figure out that logic.
Also, the JSON always returns groups in the field names, can’t find a param to turn that off.
@tomisin do you have any repeats in your data? As far as I’m aware, csv is great for scenarios where there are no repeats, but if there are repeat groups then can’t access that data via csv (unless you know of a way?)
Thanks all
hello kobo team
I am still facing the same problem there is some missing column on the power BI after I make connection using API. please help me i have Search discussion on the coming days.
thanks a lot
@sara_7500, linking you to this post so that you could benefit from the discussion that has happened here.
Well done @nat
Yes, I have repeat and nested questions, and extracting them to power bi is straightforward with all results returned clearly.
Kind regards
Hi
I face the same problem on missing fields in power bi , i am not understanding step number 2.
I have now logged an enhancement request in the Suggestion box for this: API - ability to get full extended submission schema - so PBI knows the structure to expect