Power BI on FHIR - Part II🔥

IT_Nurse
Apr 26
9 min read

Updated: May 2

Hello! Welcome to the second post in my series about visualizing HL7 FHIR data in Microsoft Power BI. As you may know, I'm currently learning about Health Information Exchange standards, and I would really like to have a firm grasp of HL7 FHIR concepts.

Last week I published Part I about how I used ChatGPT to help me with the following:

Install and run a HAPI FHIR server on my laptop
Generate synthetic patient demographic data from Synthea
Load the synthetic patient data into my FHIR server
Use my FHIR server as the data source for a Power BI report.

I ran into issues with loading some of the synthetic patients to my FHIR server, so this week I have decided to go in a slightly different direction and ask ChatGPT to help me come up with a version that instead reads in the patient demographic information from an Excel workbook using Python.

Before we get to the fun technical stuff, I'm going to take a quick detour to share some of my understanding of what HL7 FHIR is and how it works.

What is HL7 FHIR?

HL7 FHIR is a Health Information Exchange standard that defines how health data should be formatted so that it can be exchanged between one electronic system and another. It's a little like addressing an envelope: generally the data elements like name, street, city, etc. all appear in a certain location and in a certain order. This makes it much easier for postal service workers to handle the mail, because they know where to find each piece of information.

Next, it's important to talk about FHIR Resources. Sticking with the post office example, it's like there's slightly different formats for addressing parcels, documents, and letters. With FHIR, there are slightly different formats for each resource. Resources are things like Patient, Observation, and Appointment, and they are generally sent and received as .xml or .json files.

Here's the JSON for a very simple Patient resource example:

{
    "resourceType": "Patient",
    "id": "123456",
    "name": [
        {
            "family": "Mouse",
            "given": [
                "Mickey"
            ]
        }
    ],
    "birthDate": "1982-04-21"
}

Here's the json for a very simple Observation resource:

Note: this is a heart rate observation for the Mickey Mouse patient above, who has id = 123456

{
    "resourceType": "Observation",
    "id": "hr-001",
    "status": "final",
    "code": {
      "coding": [
        {
          "system": "http://loinc.org",
          "code": "8867-4",
          "display": "Heart rate"
        }
      ],
      "text": "Heart rate"
    },
    "subject": {
      "reference": "Patient/123456"
    },
    "effectiveDateTime": "2024-04-26T09:05:00-03:00",
    "valueQuantity": {
      "value": 72,
      "unit": "beats/minute",
      "system": "http://unitsofmeasure.org",
      "code": "/min"
    }
  }

Let's talk about these two resources using a real-world example:

Mickey Mouse presents to Disney Hospital because he is experiencing shortness of breath.
The registration clerk enters Mickey into their Electronic Health Record system (let's call the system Magic). Mickey’s name and birthdate are entered and saved as structured data inside Magic’s internal database — but not yet as FHIR resources.
A nurse takes Mickey’s heart rate and records a value of 72 beats per minute. This value is stored in the Heart Rate field of Magic’s database.
Depending on the system’s setup, Magic may automatically generate FHIR Patient and Observation resources and save them into a FHIR server that sits alongside the EHR. Or, Magic might dynamically create the FHIR resources only when an external application requests them.
Let’s say Mickey’s primary care provider (aka family doctor) is Doc McStuffins. Doc uses an Electronic Health Record system called Castle. Every night, Castle sends a request to the Disney Hospital FHIR server, asking for updated information on Doc's patients.
The FHIR server responds with the Patient and Observation resources for Mickey. Because Castle understands FHIR, Doc McStuffins can immediately view that Mickey had a heart rate of 72 bpm recorded at 2024-04-26T09:05:00-03:00 — without any need for manual re-entry or special translation.

This is the power of FHIR: even though Disney Hospital and Doc McStuffins use different systems, they can share patient data safely, quickly, and accurately. There's lots more content I could cover about how FHIR works, but for now I'll leave you with these links:

The official HL7 FHIR website
The patient resource documentation
The observation resource documentation

Now for the really fun stuff!

Let's pretend we have this patient data in Magic's internal database:

How can we start with this data and end up with Patient resources? I asked ChatGPT, and it gave me this Python code to read the patient from an excel worksheet and create Patient resources, saving each as a .json file. If you are familiar with the Dictionary data structure in Python, you'll notice that the patient_resource (in bold below) is basically just a Python Dictionary with the key value pairs that are in the right order to align with the structure published in the patient resource documentation on the FHIR website.

import pandas as pd
import json
import datetime
import os

# Configuration
excel_file = r"C:\fake_patients.xlsx"  # Input file
output_folder = r"C:\patients_json"    # New folder to store files

# Create output folder if it doesn't exist
os.makedirs(output_folder, exist_ok=True)

# Load the Excel file
df = pd.read_excel(excel_file)

# Loop through each patient in the excel file. Read in each patient's 
# data, and use the data to build the patient resource.
for index, row in df.iterrows():
    # Format birth date safely
    birthdate_value = row["BirthDate"]
    if isinstance(birthdate_value, (datetime.datetime, datetime.date)):
        birthdate_str = birthdate_value.strftime("%Y-%m-%d")
    else:
        birthdate_str = str(birthdate_value)

    # Build the Patient resource
    patient_resource = {
        "resourceType": "Patient",
        "identifier": [{
            "system": "http://example.org/healthcardnumber",
            "value": str(row["Health Card Number"])
        }],
        "name": [{
            "family": row["LastName"],
            "given": [row["FirstName"]]
        }],
        "gender": row["Gender"].lower(),
        "birthDate": birthdate_str,
        "telecom": [
            {"system": "phone", "value": row["PhoneNumber"], "use": "home"},
            {"system": "email", "value": row["Email"], "use": "home"}
        ],
        "address": [{
            "city": row["City"],
            "state": row["State"],
            "postalCode": str(row["PostalCode"]),
            "country": row["Country"]
        }]
    }

    # Define a nice file name like "Mickey_Mouse.json"
    safe_name = f"{row['FirstName']}_{row['LastName']}".replace(" ", "_")
    output_path = os.path.join(output_folder, f"{safe_name}.json")

    # Save to file as pretty-printed JSON
    with open(output_path, "w", encoding="utf-8") as f:
        json.dump(patient_resource, f, indent=2)

    print(f"✅ Saved {safe_name}.json")

print("\n📂 All patient JSON files are saved in:", output_folder)

Here's one of the .json files created by the script (opened in Notepad):

Great! Now that we know how to create Patient resources, and we can see what they look like once they are created, can we extend the code to also push the resources to my HAPI FHIR server? Also, can we make sure we deal with the following?

If the patient already exists on the FHIR server, don't add a duplicate. However, if an existing patient's information is updated in the spreadsheet, and the Python code is re-run, make sure the patient's information is updated on the FHIR server.

Here's the code ChatGPT provided:

import pandas as pd
import requests
import json
import datetime
import os

# ============================
# Configuration
# ============================

excel_file = r"C:\fake_patients.xlsx"  # Path to your Excel file
server_url = "http://localhost:8080/fhir"      # FHIR server base URL
headers = {"Content-Type": "application/fhir+json"}  # HTTP headers for FHIR requests

# ============================
# Helper Function: Upload a Patient (POST or PUT)
# ============================

def upload_patient(patient_resource, health_card_number, first_name, last_name, log_list):
    """
    Uploads a patient resource to the FHIR server.
    Performs search to detect existing patients based on Health Card Number.
    Updates existing patient (PUT) or creates new patient (POST).
    Appends action result to the log list.
    """

    # Step 1: Search for existing patient by Health Card Number
    search_url = f"{server_url}/Patient?identifier=http://example.org/healthcardnumber|{health_card_number}"
    search_response = requests.get(search_url, headers=headers)

    if search_response.status_code == 200:
        search_results = search_response.json()

        # Step 2: If patient exists, perform PUT to update
        if search_results.get("total", 0) > 0:
            existing_id = search_results["entry"][0]["resource"]["id"]

            # Add the existing ID to the patient resource for PUT
            patient_resource["id"] = existing_id

            put_url = f"{server_url}/Patient/{existing_id}"
            put_response = requests.put(put_url, headers=headers, json=patient_resource)

            if put_response.status_code in [200, 201]:
                print(f"✅ Updated: {first_name} {last_name}")
                log_list.append({"Action": "Update", "Name": f"{first_name} {last_name}", "Status": put_response.status_code})
            else:
                print(f"⚠️ Error updating {first_name} {last_name}: {put_response.status_code}")
                log_list.append({"Action": "Update Failed", "Name": f"{first_name} {last_name}", "Status": put_response.status_code})

        else:
            # Step 3: If no patient found, perform POST to create
            post_url = f"{server_url}/Patient"
            post_response = requests.post(post_url, headers=headers, json=patient_resource)

            if post_response.status_code in [200, 201]:
                print(f"✅ Created: {first_name} {last_name}")
                log_list.append({"Action": "Create", "Name": f"{first_name} {last_name}", "Status": post_response.status_code})
            else:
                print(f"⚠️ Error creating {first_name} {last_name}: {post_response.status_code}")
                log_list.append({"Action": "Create Failed", "Name": f"{first_name} {last_name}", "Status": post_response.status_code})

    else:
        # Search failed
        print(f"⚠️ Search failed for {first_name} {last_name}: {search_response.status_code}")
        log_list.append({"Action": "Search Failed", "Name": f"{first_name} {last_name}", "Status": search_response.status_code})

# ============================
# Main Program: Load Excel and Process Patients
# ============================

# Load the Excel file into a DataFrame
df = pd.read_excel(excel_file)

# Initialize a log list to capture results
log = []

# Process each patient in the Excel file
for index, row in df.iterrows():
    # Safely format the birth date
    birthdate_value = row["BirthDate"]
    if isinstance(birthdate_value, (datetime.datetime, datetime.date)):
        birthdate_str = birthdate_value.strftime("%Y-%m-%d")
    else:
        birthdate_str = str(birthdate_value)

    # Build the full Patient resource according to FHIR standard
    patient_resource = {
        "resourceType": "Patient",
        "identifier": [{
            "system": "http://example.org/healthcardnumber",
            "value": str(row["Health Card Number"])
        }],
        "name": [{
            "family": row["LastName"],
            "given": [row["FirstName"]]
        }],
        "gender": row["Gender"].lower(),
        "birthDate": birthdate_str,
        "telecom": [
            {"system": "phone", "value": row["PhoneNumber"], "use": "home"},
            {"system": "email", "value": row["Email"], "use": "home"}
        ],
        "address": [{
            "city": row["City"],
            "state": row["State"],
            "postalCode": str(row["PostalCode"]),
            "country": row["Country"]
        }]
    }

    # Call the helper function to upload this patient
    upload_patient(
        patient_resource,
        row["Health Card Number"],
        row["FirstName"],
        row["LastName"],
        log
    )

# ============================
# Save Upload Log to CSV
# ============================

# Convert the log list to a DataFrame
log_df = pd.DataFrame(log)

# Save the log to CSV file for review
log_file_path = r"C:\synthea\fhir_upload_log.csv"  # Adjust path if needed
log_df.to_csv(log_file_path, index=False)

print("\n📄 Upload finished. Log saved to:", log_file_path)

Before running the code, I made sure to launch my Docker app and run these three commands in a command window to ensure my local FHIR server was running with a fresh new instance:

To stop your server and remove the running container: docker rm -f hapi-fhir
To remove the volume: docker volume rm hapi_data
To recreate a clean server: docker run -d -p 8080:8080 --name hapi-fhir -v hapi_data:/hapi-fhir-jpaserver-start/db hapiproject/hapi:latest

I then opened a browser and navigated to http://localhost:8080/fhir/Patient?_pretty=true. I was able to see all of the patients from my spreadsheet, successfully loaded to my local FHIR server. Note: If you haven't read the first post in this blog series, and these last two paragraphs didn't make sense, I'd suggest taking a look.

Here's one of the patients (Minnie Mouse) that I can see in my web browser:

Okay! Last step!

In my previous blog post I took patient data (that I got from Synthea) and posted it to my HAPI FHIR server, then used the FHIR server as the source for a Power BI report. I thought that since I had cleaned off my FHIR server and restarted it, then posted the patients from my excel worksheet to it, that it might be possible to just refresh my Power BI report and see Mickey and Minnie and their crew. Unfortunately it didn't work out that way, but this was definitely an example of learning more through errors than when everything works perfectly the first time! The problem was that the data I took from Synthea was slightly different than the data I had in my spreadsheet. For example, my spreadsheet had fields for HealthCardNumber while Synthea didn't, and Synthea had Marital Status which my spreadsheet didn't. I needed to keep the HealthCardNumber as it is used in my Python code as the unique identifier to check and see if that patient already exists on the server. I also wanted to be able to see the HealthCardNumber in my Power BI report. This meant that I needed my m code in Power Query to account for the changes.

Here's the new m code:

let
    // Step 1: Load and extract patient entries
    Source = Json.Document(Web.Contents("http://localhost:8080/fhir/Patient?_count=100&_summary=false")),
    PatientEntries = Source[entry],
    PatientTable = Table.FromList(PatientEntries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedTopLevel = Table.ExpandRecordColumn(PatientTable, "Column1", {"fullUrl", "resource"}, {"FullUrl", "Resource"}),

    // Step 2: Expand core resource fields
    ExpandedResource = Table.ExpandRecordColumn(
        ExpandedTopLevel,
        "Resource",
        {"id", "identifier", "gender", "birthDate", "address", "maritalStatus", "telecom", "name"},
        {"ID", "Identifier", "Gender", "BirthDate", "Address", "MaritalStatus", "Telecom", "Name"}
    ),

    // Step 3: Extract Health Card Number from Identifier
    ExpandedIdentifierList = Table.ExpandListColumn(ExpandedResource, "Identifier"),
    ExpandedIdentifier = Table.ExpandRecordColumn(ExpandedIdentifierList, "Identifier", {"system", "value"}, {"IdentifierSystem", "HealthCardNumber"}),

    // Filter to keep only Health Card Number identifiers
    FilteredIdentifier = Table.SelectRows(ExpandedIdentifier, each [IdentifierSystem] = "http://example.org/healthcardnumber"),
    RemovedIdentifierSystem = Table.RemoveColumns(FilteredIdentifier, {"IdentifierSystem"}),


    // Step 4: Keep only the first name entry, then extract given/family
    FirstNameOnly = Table.TransformColumns(ExpandedIdentifier, {{"Name", each if List.IsEmpty(_) then null else _{0}}}),
    ExpandedName = Table.ExpandRecordColumn(FirstNameOnly, "Name", {"given", "family"}, {"GivenNames", "FamilyName"}),
    CombinedGivenNames = Table.TransformColumns(ExpandedName, {{"GivenNames", each Text.Combine(_, " "), type text}}),

    // Step 5: Expand and flatten address
    ExpandedAddressList = Table.ExpandListColumn(CombinedGivenNames, "Address"),
    ExpandedAddress = Table.ExpandRecordColumn(ExpandedAddressList, "Address", {"city", "state", "postalCode", "country"}, {"City", "State", "PostalCode", "Country"}),

    // Step 6: Expand telecom and format display
    ExpandedTelecomList = Table.ExpandListColumn(ExpandedAddress, "Telecom"),
    ExpandedTelecom = Table.ExpandRecordColumn(ExpandedTelecomList, "Telecom", {"system", "value"}, {"TelecomSystem", "TelecomValue"}),
    FilteredTelecom = Table.SelectRows(ExpandedTelecom, each ([TelecomSystem] = "phone")),

    // Step 7: Expand marital status text
    ExpandedMaritalStatus = Table.ExpandRecordColumn(FilteredTelecom, "MaritalStatus", {"text"}, {"MaritalStatus"}),

    // Step 8: Clean up birth date and calculate age
    ParsedBirthDate = Table.TransformColumns(ExpandedMaritalStatus, {{"BirthDate", each try Date.From(_) otherwise null, type nullable date}}),
    AddedAge = Table.AddColumn(ParsedBirthDate, "Age", each if [BirthDate] <> null then Number.RoundDown(Duration.Days(Date.From(DateTime.LocalNow()) - [BirthDate]) / 365) else null, Int64.Type)
in
    AddedAge

I'd like to point out that it took several tries to find the correct code to bring in the HealthCardNumber. It turns out that it's important to use this code:

Source = Json.Document(Web.Contents("http://localhost:8080/fhir/Patient?_count=100&_summary=false")),

instead of this code:

 Source = Json.Document(Web.Contents("http://localhost:8080/fhir/Patient?_count=100")),

because otherwise the HealthCardNumber in the Identifier field doesn't come across.

However, once I found my way around that mix-up, I was able to refresh my report and see my patients! I'm also able to update my Excel file, re-run the Python Code, and then see the changes when I refresh the Power BI report again 🎉

As a reminder, here's the data from the spreadsheet:

And that brings us to the end of Part II of this project! Next up, I'd also like to create .json files different FHIR resources like Encounters and Observations.

If you have any thoughts or feedback about what I came up with, or how I went about it, I would love to hear from you in the comments section below. I'd also love to know if you have any related projects you would like to share. In any case, I hope you have a fantastic day!

Lisa

LISATOTTON

Power BI on FHIR - Part II🔥

Recent Posts

Comments

Contact