Processing HL7 FHIR nested objects using Power BI and Power Query!

The FHIR standard makes healthcare data interoperability easier than the previous standards. However, learning how to work with FHIR still requires an investment of time. Recently my team built a relatively simple questionnaire that can be used in a hospital setting to survey patients. We focused on quality-of-life survey, but the FHIR standard also supports clinical surveys. We leveraged an application that we’ve used to build out various parts of the FHIR standard for demonstration purposes. Our goal was to allow a clinician with a mobile device to interview patients on their hospital stay and capture their responses. We then provide a dashboard that aggregates the responses from the patients for analysis.

For this article, I’ll focus on how we pulled the FHIR data into PowerBI and not on the Questionnaire and QuestionnaireResponse resources of the FHIR spec. Because of the complexity of the FHIR data, the resulting data is a collection of nested objects that need to be flattened for use in analytics. We will use the Power Query Connector for FHIR. To use this connector, you need the URL of your FHIR API service and an account with permissions to query the API. Once connected and are able to pull the data from your FHIR API, you will notice that some of the columns have nested List, Record and Table objects. This is the way the connector works to support the various complex FHIR structures. Fortunately, Power Query can flatten this data. Let’s look at a couple of examples of the Record and Table nested objects. You can use the same technique to process List objects.

The image below shows how both the Record and Table objects are represented in the data returned by the connector.

Record

As shown below, the record object is just a list of key-value pairs.

To extract the values and add them as fields to your base table, we’ll use the "expand" icon on the top right of the column name. Click on the expand icon and select the fields you want to add to your base table. For our example we’ll just bring in the author’s type and name.

Table

The table object is a bit more interesting since it can contain more than one record as show below.

To process and add the records to your base table, you’ll use the expand icon on the top right corner of the field name.

In the same way as we did for the Record object, select which columns of the table you want to add to your base table. We’ll add the linkId, text and answer columns. Note that the answer column is a nested table, and we’ll need to repeat the process to expand it and bring in the answer values.

Final

When you are done processing, you will have a final output that looks like the image below. The highlighted columns are the ones derived from the nested objects.

Instead of using the UI, the same could be accomplished by using the PowerBI Advanced Editor and writing M code. Since PowerBI translates the data transformation steps into code, you can see the code that was generated by looking at the advanced editor tab. Here’s what was generated from the steps we just went through.

Summary

This shows how to use the PowerBI Power Query editor to transform HL7 FHIR nested data into a tabular format. We used a simple questionnaire to demonstrated this. It’s possible that some of the questionnaires you will work with will not be as straightforward. In the next article, I’ll show how you can use Azure Synapse to work with a complex questionnaire. We’ll leverage the $export capability of FHIR to export data to storage account and then use Spark to process the files and generate a tabular structure for analytics.