From Messy Hospital Spreadsheets to Life-Saving Decisions – Power BI in Action
(Using Realistic Hospital & Pharmacy Mock Data)
In the real world, data is rarely clean, complete, or well-structured. Analysts are often handed spreadsheets exported from multiple systems, each with its own logic and inconsistencies. Power BI becomes powerful not because it creates charts, but because it helps analysts turn disorder into decisions.
Using a mock hospital and pharmacy dataset, this article demonstrates how analysts translate messy data, DAX, and dashboards into real-world action using Power BI.
1. Starting with the Business Question (Analytics Mindset)
Before opening Power BI, analysts must understand why the analysis exists.
Mock Business Questions
Using a hospital and pharmacy dataset, stakeholders might ask:
- Which hospitals have the highest patient volumes?
- Are some pharmacies experiencing frequent stock-outs?
- Which departments are driving higher treatment costs?
- Are patient wait times increasing over time?
Without these questions, even the best dashboard becomes noise.
2. Messy Data Reality: What Analysts Actually Receive
Mock Raw Data Snapshot
Hospital Admissions
| Patient_ID | Hospital | Admit_Date | Dept | Wait_Time |
|---|---|---|---|---|
| P-001 | KNH | 12/01/24 | ER | 45 |
| P-002 | knh | 13-01-2024 | Emergency | NULL |
| P-003 | MTRH | 14/1/24 | OPD | 30 |
Pharmacy Transactions
| Drug_Name | Qty | Cost | Facility |
|---|---|---|---|
| Paracetamol | 100 | 10 | KNH |
| paracetamol | -5 | 10 | KNH |
| Amoxicillin | 50 | 25 | MTRH |
Issues immediately visible:
- Inconsistent naming (KNH vs knh)
- Different date formats
- Missing values
- Negative quantities
- Unstandardized department names
This is where Power Query becomes essential.
3. Cleaning and Shaping Data with Power Query
Using Power Query, analysts:
- Standardize hospital names (KNH, knh → Kenyatta National Hospital)
- Replace null wait times with averages
- Remove negative quantities
- Convert dates into a consistent format
- Split and rename columns for clarity
Example Transformation
4. Building Relationships: Connecting Hospital & Pharmacy Data
A star star schema is used to build the relationship on the pharmacy and hospital data
The Schema ensures:
- Correct aggregation of costs and admissions
- Accurate filtering across hospitals and time
- Faster report performance
5. Translating Numbers into Meaning with DAX
Raw totals don’t drive decisions. Measures do.
Mock DAX Measures
Total Patients
Total Patients = COUNT(Fact_Admissions[Patient_ID])
**Average Waiting Time**
dax
Total Pharmacy Cost = SUMX(
Fact_Pharmacy,
Fact_Pharmacy[Qty] * Fact_Pharmacy[Cost]
)
Now stakeholders can finally answer key business questions:
- Which hospitals have the longest waits?
- Where is pharmacy spending highest?
- How does performance change month to month?
6. Designing Dashboards That Drive Decisions
Mock Dashboard Layout
7. Turning Insights into Action
Mock Insight → Action Flow
Key Insights Identified
- Hospital A has the highest patient load but below-average staffing
- Pharmacy stock-outs spike every end of month
- Emergency department wait times exceed 60 minutes
Actions Triggered
- Reallocate staff during peak hours
- Adjust procurement schedules to prevent end-of-month shortages
- Introduce triage optimization in Emergency units
This is the true value of Power BI: evidence-backed decisions that directly improve hospital operations and patient care.
8. Why This Matters in the Real World
In healthcare, poor decisions have real, sometimes life-altering consequences. Power BI empowers analysts and decision-makers to achieve:
- Faster response to operational risks
- Better resource allocation across facilities and departments
- Improved patient outcomes through timely interventions
- Accountability through transparent, data-driven reporting
For analysts, mastering Power BI means evolving from someone who simply builds reports into a true problem-solver who influences real-world outcomes and contributes to better healthcare delivery.
Conclusion
Messy data is not a barrier - it’s the starting point.
By cleaning data with Power Query, modeling it correctly, writing purposeful DAX, and designing decision-focused dashboards, analysts transform raw hospital and pharmacy data into meaningful, actionable insight.
Power BI is not about charts.
It’s about turning complexity into clarity and clarity into impact.
Top comments (0)