FHIR Questionnaires - flattening the nested Item structure

[TerriblyVexed]

Hello,

This request for help is regarding using Power BI to "flatten"/"deserialize" the nested Item data structure of Questionnaires.

I'm using the Fhir Connector to connect to my FHIR server. The ask from my PM is to render the Questionnaires in navigable fashion in Power BI with an eye towards storing the data in a persistence layer (SQL Server has been chosen). We have 13 Questionnaires loaded in FHIR server now. Some of them are standard but there will be more and they wil be custom. So for instance we have the Cancer Screening Questionnaire from the HL7 website. https://www.hl7.org/fhir/questionnaire-example.json.html 

Here is my Power Query for Questionnaire:

let
    Source = Fhir.Contents("https://{myserver}.azurewebsites.net", null),
    Questionnaire1 = Source{[Name = "Questionnaire"]}[Data],
    #"Expanded subjectType" = Table.ExpandListColumn(Questionnaire1, "subjectType"),
    #"Expanded useContext" = Table.ExpandTableColumn(#"Expanded subjectType", "useContext", {"code", "value"}, {"useContext.code", "useContext.value"}),
    #"Expanded useContext.code" = Table.ExpandRecordColumn(#"Expanded useContext", "useContext.code", {"system", "version", "code", "display", "userSelected"}, {"useContext.code.system", "useContext.code.version", "useContext.code.code", "useContext.code.display", "useContext.code.userSelected"}),
    #"Expanded useContext.value" = Table.ExpandRecordColumn(#"Expanded useContext.code", "useContext.value", {"CodeableConcept", "Quantity", "Range", "Reference"}, {"useContext.value.CodeableConcept", "useContext.value.Quantity", "useContext.value.Range", "useContext.value.Reference"}),
    #"Expanded useContext.value.CodeableConcept" = Table.ExpandRecordColumn(#"Expanded useContext.value", "useContext.value.CodeableConcept", {"coding", "text"}, {"useContext.value.CodeableConcept.coding", "useContext.value.CodeableConcept.text"}),
    #"Expanded useContext.value.CodeableConcept.coding" = Table.ExpandTableColumn(#"Expanded useContext.value.CodeableConcept", "useContext.value.CodeableConcept.coding", {"system", "version", "code", "display", "userSelected"}, {"useContext.value.CodeableConcept.coding.system", "useContext.value.CodeableConcept.coding.version", "useContext.value.CodeableConcept.coding.code", "useContext.value.CodeableConcept.coding.display", "useContext.value.CodeableConcept.coding.userSelected"}),
    #"Expanded useContext.value.Quantity" = Table.ExpandRecordColumn(#"Expanded useContext.value.CodeableConcept.coding", "useContext.value.Quantity", {"value", "comparator", "unit", "system", "code"}, {"useContext.value.Quantity.value", "useContext.value.Quantity.comparator", "useContext.value.Quantity.unit", "useContext.value.Quantity.system", "useContext.value.Quantity.code"}),
    #"Expanded useContext.value.Range" = Table.ExpandRecordColumn(#"Expanded useContext.value.Quantity", "useContext.value.Range", {"low", "high"}, {"useContext.value.Range.low", "useContext.value.Range.high"}),
    #"Expanded useContext.value.Range.low" = Table.ExpandRecordColumn(#"Expanded useContext.value.Range", "useContext.value.Range.low", {"value", "comparator", "unit", "system", "code"}, {"useContext.value.Range.low.value", "useContext.value.Range.low.comparator", "useContext.value.Range.low.unit", "useContext.value.Range.low.system", "useContext.value.Range.low.code"}),
    #"Expanded useContext.value.Range.high" = Table.ExpandRecordColumn(#"Expanded useContext.value.Range.low", "useContext.value.Range.high", {"value", "comparator", "unit", "system", "code"}, {"useContext.value.Range.high.value", "useContext.value.Range.high.comparator", "useContext.value.Range.high.unit", "useContext.value.Range.high.system", "useContext.value.Range.high.code"}),
    #"Expanded jurisdiction" = Table.ExpandTableColumn(#"Expanded useContext.value.Range.high", "jurisdiction", {"coding", "text"}, {"jurisdiction.coding", "jurisdiction.text"}),
    #"Expanded jurisdiction.coding" = Table.ExpandTableColumn(#"Expanded jurisdiction", "jurisdiction.coding", {"system", "version", "code", "display", "userSelected"}, {"jurisdiction.coding.system", "jurisdiction.coding.version", "jurisdiction.coding.code", "jurisdiction.coding.display", "jurisdiction.coding.userSelected"}),
    #"Expanded effectivePeriod" = Table.ExpandRecordColumn(#"Expanded jurisdiction.coding", "effectivePeriod", {"start", "end"}, {"effectivePeriod.start", "effectivePeriod.end"})
in
    #"Expanded effectivePeriod"

When working with this json data (after calling the Questionnaire resource vi the Fhir Connector) you come across this nested Item structure. What I mean is that Item can be nested several layers and there is no way to know how deep it goes. Because of this it isn't feasible to sit there and continually Expand the Item column manually each time a new Questionnaire is added to FHIR Server. 

So first issue is how to automatically expand the Item.Item.Item.Item.Item.... structure only as far as there is data present for each level and for each Questionnaire. I don't want to arbitrarily expand it to say 10 levels, hoping that any given Questionnaire never exceeds that. 

Next I need a way to present the data. I think a tabular representation would be best, but I would like to know how others have done this. So far I have the first page in Power BI set up like so:

As you can see I only have "header" level information and basic slicers. I next need to display the Item.Item.Item structure in a meaningful way. If I were a clinician how would I most like to interact with this data? What I was thinking, but I am open to suggestions, is having a Matrix visual do the traversing. But I can't lose fidelity to the parent. The LinkId is a freeform field, so the only thing reliably constraining one level to it's parent is its position in the nest. 

So far I have used the Fhir Connector to call the Questionnaire API Response and then taken that as the starting point for a new table called QuestionnaireItem. That way I don't have to do more than one call. 

Here is my Power Query:

let
    Source = Questionnaire,
    #"Removed Columns" = Table.RemoveColumns(Source,{"date", "publisher", "contact", "description", "useContext.code.system", "useContext.code.version", "useContext.code.code", "useContext.code.display", "useContext.code.userSelected", "useContext.value.CodeableConcept.coding.system", "useContext.value.CodeableConcept.coding.version", "useContext.value.CodeableConcept.coding.code", "useContext.value.CodeableConcept.coding.display", "useContext.value.CodeableConcept.coding.userSelected", "useContext.value.CodeableConcept.text", "useContext.value.Quantity.value", "useContext.value.Quantity.comparator", "useContext.value.Quantity.unit", "useContext.value.Quantity.system", "useContext.value.Quantity.code", "useContext.value.Range.low.value", "useContext.value.Range.low.comparator", "useContext.value.Range.low.unit", "useContext.value.Range.low.system", "useContext.value.Range.low.code", "useContext.value.Range.high.value", "useContext.value.Range.high.comparator", "useContext.value.Range.high.unit", "useContext.value.Range.high.system", "useContext.value.Range.high.code", "useContext.value.Reference", "jurisdiction.coding.system", "jurisdiction.coding.version", "jurisdiction.coding.code", "jurisdiction.coding.display", "jurisdiction.coding.userSelected", "jurisdiction.text", "purpose", "copyright", "approvalDate", "lastReviewDate", "effectivePeriod.start", "effectivePeriod.end", "code", "experimental", "derivedFrom", "identifier", "version", "name", "title", "text", "contained", "extension", "modifierExtension", "meta", "implicitRules", "language"}),
    #"Expanded item" = Table.ExpandTableColumn(#"Removed Columns", "item", {"extension", "linkId", "code", "text", "type", "answerValueSet", "answerOption", "item"}, {"item.extension", "item.linkId", "item.code", "item.text", "item.type", "item.answerValueSet", "item.answerOption", "item.item"}),
    #"Expanded item.extension" = Table.ExpandTableColumn(#"Expanded item", "item.extension", {"extension", "url", "value"}, {"item.extension.extension", "item.extension.url", "item.extension.value"}),
    #"Expanded item.extension.value" = Table.ExpandRecordColumn(#"Expanded item.extension", "item.extension.value", {"base64Binary", "boolean", "canonical", "code", "date", "dateTime", "decimal", "id", "instant", "integer", "markdown", "oid", "positiveInt", "string", "time", "unsignedInt", "uri", "url", "uuid", "Address", "Age", "Annotation", "Attachment", "CodeableConcept", "Coding", "ContactPoint", "Count", "Distance", "Duration", "HumanName", "Identifier", "Money", "Period", "Quantity", "Range", "Ratio", "Reference", "SampledData", "Signature", "Timing", "ContactDetail", "Contributor", "DataRequirement", "Expression", "ParameterDefinition", "RelatedArtifact", "TriggerDefinition", "UsageContext", "Dosage", "Meta"}, {"item.extension.value.base64Binary", "item.extension.value.boolean", "item.extension.value.canonical", "item.extension.value.code", "item.extension.value.date", "item.extension.value.dateTime", "item.extension.value.decimal", "item.extension.value.id", "item.extension.value.instant", "item.extension.value.integer", "item.extension.value.markdown", "item.extension.value.oid", "item.extension.value.positiveInt", "item.extension.value.string", "item.extension.value.time", "item.extension.value.unsignedInt", "item.extension.value.uri", "item.extension.value.url", "item.extension.value.uuid", "item.extension.value.Address", "item.extension.value.Age", "item.extension.value.Annotation", "item.extension.value.Attachment", "item.extension.value.CodeableConcept", "item.extension.value.Coding", "item.extension.value.ContactPoint", "item.extension.value.Count", "item.extension.value.Distance", "item.extension.value.Duration", "item.extension.value.HumanName", "item.extension.value.Identifier", "item.extension.value.Money", "item.extension.value.Period", "item.extension.value.Quantity", "item.extension.value.Range", "item.extension.value.Ratio", "item.extension.value.Reference", "item.extension.value.SampledData", "item.extension.value.Signature", "item.extension.value.Timing", "item.extension.value.ContactDetail", "item.extension.value.Contributor", "item.extension.value.DataRequirement", "item.extension.value.Expression", "item.extension.value.ParameterDefinition", "item.extension.value.RelatedArtifact", "item.extension.value.TriggerDefinition", "item.extension.value.UsageContext", "item.extension.value.Dosage", "item.extension.value.Meta"}),
    #"Expanded item.extension.value.Attachment" = Table.ExpandRecordColumn(#"Expanded item.extension.value", "item.extension.value.Attachment", {"contentType", "language", "data", "url", "size", "hash", "title", "creation"}, {"item.extension.value.Attachment.contentType", "item.extension.value.Attachment.language", "item.extension.value.Attachment.data", "item.extension.value.Attachment.url", "item.extension.value.Attachment.size", "item.extension.value.Attachment.hash", "item.extension.value.Attachment.title", "item.extension.value.Attachment.creation"}),
    #"Expanded item.extension.value.CodeableConcept" = Table.ExpandRecordColumn(#"Expanded item.extension.value.Attachment", "item.extension.value.CodeableConcept", {"coding", "text"}, {"item.extension.value.CodeableConcept.coding", "item.extension.value.CodeableConcept.text"}),
    #"Expanded item.extension.value.CodeableConcept.coding" = Table.ExpandTableColumn(#"Expanded item.extension.value.CodeableConcept", "item.extension.value.CodeableConcept.coding", {"system", "version", "code", "display"}, {"item.extension.value.CodeableConcept.coding.system", "item.extension.value.CodeableConcept.coding.version", "item.extension.value.CodeableConcept.coding.code", "item.extension.value.CodeableConcept.coding.display"}),
    #"Expanded item.extension.value.Coding" = Table.ExpandRecordColumn(#"Expanded item.extension.value.CodeableConcept.coding", "item.extension.value.Coding", {"system", "version", "code", "display"}, {"item.extension.value.Coding.system", "item.extension.value.Coding.version", "item.extension.value.Coding.code", "item.extension.value.Coding.display"})
in
    #"Expanded item.extension.value.Coding"

 This is Questionnaire.Item expanded one level and already it's messy and overwhelming. 

I have tried to do a Matrix but b/c of the nature of the numerous possible fields and values it doesn't look clean. The Item and AnswerOptions can be stored in Item.Extension fields, or Item.Code fields, or Item.CodeableConcept fields, or thier own Item.Boolean, Item.String, Item.Date, etc etc ad nauseum.

Thank in advance.

Vexed

[TerriblyVexed]

@jennratten Thank you!

Your post lead me down the rabbit hole to a workable solution. Using your links and @ImkeF stuff I now have a workable solution to move forward with.

https://community.powerbi.com/t5/Desktop/Rest-API-Json-several-pages-automatically-call-the-next-pag... 

if you scroll to the end you'll see my post with the final code.

Adding here for others:

let 
    baseUrl = "https://{myurl}.azurewebsites.net",
    resource = "/Questionnaire",
    search = "?&_total=accurate",
    fullUrl = baseUrl & resource & search,

    // define the function
    FnGetOnePage =
        (url) as record =>
        let
            Source = Json.Document(Web.Contents(url)),
            data = try Source[entry] otherwise null,
            // link is list of records
            link = try Source[link] otherwise null,
            // link{0} could be "self", "next"
            next = if link{0}[relation] = "next"
                    then link{0}[url]
                    else null,
            res = [Data=data, Next=next]
        in
            res,
            
    // use the FnGetOnePage function with List.Generate    
    GeneratedList =
        List.Generate( ()=> 
            [result = FnGetOnePage(fullUrl)],
            // do while
            each [result][Data] <> null,
            // each row of list
            each [result = FnGetOnePage([result][Next])],
            // output
            each [result][Data]
            ),
    // convert to table from the output of function GeneratedList
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand Table" = funcExpand(GeneratedList)
in
    #"Expand Table"

https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po... 

Vexed

[jennratten · Accepted solution]

Hello - 

Note - I have not worked with FHIR but have done similar projects. 

These pages should get you going with regards to dynamically expanding all nested tables/objects (aka flattening).  You will likely need to customize to your specific situation, but it should be relatively easy to do.

https://www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-po... 

https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/ 

In terms of shaping the data in a way that can be visualized, after your data has been flattened, I recommend normalizing the data as much as possible, come up with some attributes/dimensions and add them via conditional columns, such as Category, Subcategory, etc.  If your data includes values that should be calculated (sums, averages, etc.), group on the attributes you created plus other relevant columns, then unpivot the calculated columns and create a data types column, like currency, integer and percent.  Create dimension tables that you can use in slicers/table visuals.  Create a few of measures in the data model - one each for currency values, integer values and percent values (so you can assign formatting appropriately).

In this snip below, the text in the Measures column are column names that I normalized.

Here is a snip of a report page - instead of slicers, I used tables.