Build a Complete Hospital Management Dashboard with Power BI, Excel, MySQL & Figma

Introduction

Hospitals generate massive amounts of data—patients, doctors, staff, appointments, beds, medicines, finances, etc. The challenge is turning this raw data into actionable insights. This article walks you through creating a fully functional, visually appealing hospital dashboard using Power BI, Excel, MySQL, and Figma, covering data import, modeling, visualization, and deployment.

1. Project Overview

• Pages: Overview, Patient, Doctor, Hospital‑wide, Finance.
• Key visuals: Upcoming appointments, bed availability, stock levels, star ratings, commission calculator, KPI cards.
• Tools used: Power BI Desktop, Excel workbooks, MySQL Workbench, Figma for design assets, custom Excel‑Uploader app.

2. Getting the Data In

2.1 Import via Excel

1. Open Power BI → Get Data → Excel Workbook.
2. Load each of the 16 source sheets (Appointments, Rates, Departments, etc.).
3. Rename tables inside Power Query for clarity (e.g., Appointment, Rates).

2.2 Import via MySQL

1. Create a schema called HospitalData in MySQL Workbench.
2. Use the Excel‑Uploader app (provided in the description) to bulk‑import CSV versions of the sheets.
3. Export the schema as a dump file for easy sharing.

2.3 Dynamic File‑Path Parameter (Pro Tip)

• Create a Power BI parameter named FilePath.
• Replace static file paths in each query with FilePath.
• When moving the project to another computer, only update the parameter once—no need to edit every query.

3. Data Modeling – From Chaos to Clean Relationships

1. Identify the master table – the Patient table (contains unique PatientID).
2. Reduce tables: merge one‑to‑one tables (e.g., SatisfactionScore, Surgery) into Patient, then hide the originals.
3. Delete all auto‑detected relationships and rebuild them manually:
4. PatientID → Appointment (one‑to‑many)
5. PatientID → PatientTest (one‑to‑many)
6. PatientID → Medicine (one‑to‑many)
7. Handle duplicates: use Distinct in SQL or Power Query Remove Duplicates before joining.
8. Create views in MySQL for simplified tables (e.g., PatientInfo, MedicalStockInfo).

4. Building the Visuals in Power BI

4.1 Calendar Table

• Use CALENDAR(MIN(PatientInfo[Date]), MAX(PatientInfo[Date])).
• Add calculated columns: Year, Month, MonthIndex, Day, etc., with FORMAT.

4.2 KPI Cards & Measures

• Total Charges, Total Quantity, Total Bill Amount, Discounted Total.
• Example DAX: TotalCharges = SUM(PatientInfo[Charges]).
• Create a Star Rating quick measure (star rating visual).

4.3 Images & URLs

• Store image URLs in the PatientInfo and DoctorInfo tables.
• In a Card visual, set Data category to Image URL to display pictures.

4.4 Slicer Panel with Bookmarks

1. Add a slicer for patient names.
2. Create two bookmarks: Filter Off and Filter On.
3. Assign the bookmarks to buttons (show/hide the slicer).
4. Turn Data off in bookmark settings to keep filters from resetting.

4.5 Design Touches

• Background image from Figma (downloaded from the description).
• Custom icons (flat icons website) – recolor to match theme.
• Rounded borders, shadows, and consistent colour palette (dark blue, light grey).

5. Bonus Tips & Resources

• Ready‑to‑use files: a single combined Excel file and a MySQL dump are provided in the video description for instant start.
• Paid dashboard: a low‑cost download gives you the fully built .pbix file and future updates.
• Coupon codes appear three times in the video; applying them makes the dashboard almost free.
• Learning path: start with Excel import → Power Query → Data Modeling → DAX → Visual Design → Publishing.

6. Publishing & Next Steps

• Save the .pbix file (HospitalDashboard.pbix).
• Publish to Power BI Service for web access and scheduled refresh.
• Continue with the next tutorial (adding advanced drill‑through, custom visuals, and automated email reports).

7. Why This Project Matters

• Demonstrates end‑to‑end data pipeline (Excel → MySQL → Power BI).
• Teaches real‑world data‑modeling strategies (master‑detail, duplicate handling, dynamic parameters).
• Provides a reusable template for any healthcare or service‑industry analytics.

Takeaway: By following the step‑by‑step process outlined above, you can transform scattered hospital data into a dynamic, insight‑driven dashboard without watching the original video. All the techniques—dynamic file paths, table reduction, MySQL views, DAX measures, and bookmark‑driven slicers—are reusable for any business intelligence project.

With a single Power BI file, a well‑structured MySQL schema, and a few design tweaks, you can turn raw hospital data into a powerful decision‑making dashboard—saving time, avoiding data‑path headaches, and delivering instant insights to clinicians and administrators alike.