Abstract
Adding in multiple answers, multiple lines of text, and Yes/No responses leads to some additional challenges. For example, we did an IT Demographic Survey of all our academic personnel at the Air Force Academy. I used the same technique as the previous chapter to copy each response to a SharePoint list using Microsoft Flow. We see the first inkling of an issue when we look at the data in SharePoint as shown in Figure 67. The extra explanatory text after Level 4 gets copied (since it was part of the answer). More significantly, the multiple answers get represented as ["EDU (wired)","MIL"]. Those with some web programming experience might recognize this as JavaScript Object Notation. When we connect to the data in Power BI, we see an additional issue in that the data is displayed as HTML as shown in Figure 68.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Notes
- 1.
This might not have happened if I had selected Plain Text in the SharePoint column properties, but users had already started responding to the survey by then.
- 2.
Note how the previous step of parsing the JSON turned the values to List instead of text.
- 3.
We could also choose Extract Values. In that case, we would pick the delimiter (comma in this case) and then we could tell it whether to make rows or columns.
- 4.
We also created a DaysPassed column as in past examples using the formula =DateTime.From(DateTime.LocalNow()) - [Created]. We can use this in future years to compare survey results over time.
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2019 Jeffrey M. Rhodes
About this chapter
Cite this chapter
Rhodes, J.M. (2019). Power BI Challenges with JSON, XML, and Yes/No Data. In: Creating Business Applications with Office 365. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4842-5331-1_12
Download citation
DOI: https://doi.org/10.1007/978-1-4842-5331-1_12
Published:
Publisher Name: Apress, Berkeley, CA
Print ISBN: 978-1-4842-5330-4
Online ISBN: 978-1-4842-5331-1
eBook Packages: Professional and Applied ComputingApress Access BooksProfessional and Applied Computing (R0)