An end-to-end Power BI project analysing patient waitlists across inpatient, day case and outpatient categories. The report highlights waitlist distribution, specialty-level behaviour and long-term monthly trends. The aim of this work is to build practical Power BI skills and present a complete report development workflow.
This project follows a full lifecycle approach. It starts with defining what needs to be measured, moves into cleaning and shaping raw CSV files and then builds an interactive report with clear navigation. The final result supports healthcare performance monitoring by showing waitlist volumes and patterns across multiple dimensions.
- How have patient waitlists changed over time across inpatient, day case and outpatient services?
- Which specialties contribute the most to overall waitlist volumes?
- How does wait time distribution vary by age profile and time band?
- How does the latest month compare with the same period in the previous year?
- Power BI Desktop
- Power Query (M) for data cleaning and transformation
- DAX for measures and time-based calculations
- Interactive report design and navigation
The dataset contains multi-year patient waitlist information split across separate files.
The dataset represents anonymised, aggregated patient waitlist counts and is intended for analytical and educational purposes only.
Inpatient
- IN_WL_2018.csv
- IN_WL_2019.csv
- IN_WL_2020.csv
- IN_WL_2021.csv
Outpatient
- OP_WL_2018.csv
- OP_WL_2019.csv
- OP_WL_2020.csv
- OP_WL_2021.csv
Mapping File
- Mapping_Specialty.csv
- Archive_Date
- Case_Type
- Specialty_Name
- Time_Bands
- Age_Profile
Folder connections were used to combine yearly files into unified inpatient and outpatient tables.
Data cleaning and shaping was done in Power Query. The main steps were:
- Combining multiple CSV files into single tables
- Checking and correcting data types
- Renaming and organising columns
- Adding calculated columns as needed
- Appending inpatient and outpatient tables
- Ensuring the data was consistent before modelling
The data model follows a straightforward star schema to support efficient filtering and performance.
Fact table
- Combined waitlist data covering all case types and years
Dimension table
- Specialty mapping table used for grouping and categorisation
Relationships were created to support clean filtering and analysis.
Key elements:
- KPI cards comparing current and previous year waitlists
- Donut visual showing distribution by case type
- Stacked bar chart linking time bands with age profiles
- Top five specialties based on waitlist volume
- Monthly trend analysis for inpatient, day case and outpatient categories
Features:
- Filter panel for archive date, case type, specialty, age profile and time bands
- Expandable grid showing detailed breakdowns
- Buttons for navigation
- Clean layout designed for deeper analysis
This report uses DAX to support dynamic calculations, time-based comparisons and user-driven behaviour across visuals. Measures are designed to respond to slicer selections, handle missing values gracefully and keep visuals consistent across pages.
Below is a selection of key measures used in the report.
Calculates the average number of patients on the waiting list.
Average Waiting List = AVERAGE(All_Data[Total])
Calculates the median waiting list value, providing a more robust measure when data is skewed.
Median Waiting List = MEDIAN(All_Data[Total])
Allows users to switch between Average and Median waiting list calculations using a slicer.
Avg/Med Wait List = SWITCH (
VALUES('Calculation Method'[Cal Method]),"Average",
[Average Waiting List],"Median", [Median Waiting List]
)
Updates chart titles automatically based on the selected calculation method to maintain clarity.
Dynamic Title = SWITCH (
VALUES('Calculation Method'[Cal Method]),"Average",
"Key Indicator - Patient Wait List (Average)","Median",
"Key Indicator - Patient Wait List (Median)"
)
Returns the total waiting list value for the most recent available month. The + 0 ensures blanks are handled cleanly in visuals.
Latest Month Wait List = CALCULATE (
SUM(All_Data[Total]),
All_Data[Archive_Date] = MAX(All_Data[Archive_Date])
) + 0
Calculates the waiting list total for the same month in the previous year.
PY Latest Month Wait List = CALCULATE (
SUM(All_Data[Total]),
All_Data[Archive_Date] = EDATE(MAX(All_Data[Archive_Date]), -12)
) + 0
Displays a message when no data is available for non-outpatient selections.
NoDataLeft = IF(
ISBLANK(
CALCULATE(
SUM(All_Data[Total]),
All_Data[Case_Type] <> "Outpatient"
)
),
"No Data for selected criteria",
""
)
Displays a message when no data is available for outpatient selections.
NoDataRight = IF(
ISBLANK(
CALCULATE(
SUM(All_Data[Total]),
All_Data[Case_Type] = "Outpatient"
)
),
"No Data for selected criteria",
""
)
This project helped reinforce:
- End-to-end Power BI report development from raw data to final delivery
- Practical use of folder-based imports for scalable data refresh
- Data modelling best practices using a star schema
- Writing reusable and dynamic DAX measures
- Designing reports with usability, navigation and clarity in mind
- Custom background images were used to create a consistent visual layout across report pages.
healthcare-waitlist-analysis/
├─ Data/
│ ├─ Inpatient/
│ │ ├─ IN_WL_2018.csv
│ │ ├─ IN_WL_2019.csv
│ │ ├─ IN_WL_2020.csv
│ │ └─ IN_WL_2021.csv
│ ├─ Outpatient/
│ │ ├─ OP_WL_2018.csv
│ │ ├─ OP_WL_2019.csv
│ │ ├─ OP_WL_2020.csv
│ │ └─ OP_WL_2021.csv
│ └─ Mapping_Specialty.csv
├─ Dax/
│ └─ Key_Measures.txt
├─ Report_Backgrounds/
│ ├─ Details.png
│ ├─ DrillDown.png
│ └─ Summary.png
├─ Healthcare_Waitlist_Analysis.pbix
├─ README.md
- Download the PBIX file.
- Keep the dataset folder in the same structure if you plan to refresh the data.
- Open the PBIX file in Power BI Desktop to explore, review or update the report (Power BI Desktop required).
Inspiration for this project was taken from publicly available educational material on YouTube.