Clinic appointments workbook
An XLSX workbook with one sheet per clinic site and a hidden notes sheet. Combine every visible site sheet and work out attendance.
intermediate · Skills: Excel.Workbook navigation, Hidden sheets, Combining sheets, Logical columns · Updated 2026-11-04
Data files
- appointments.xlsx — Sheets Northside and Riverside (9 appointments each) and a hidden Lookup notes sheet.Download XLSX (11.5 KB)
Queries
Validated in the Power Query Exercises simulator against the files above. Paste them into Excel or Power BI yourself; the header comments explain each source binding.
All appointments all-appointments.pq
Download .pqEvery visible site sheet combined, with the site name. Returns 18 rows: Site, ApptID, Date, Clinician, Type, DurationMin, Attended.
// All appointments
// Pack: Clinic appointments workbook (Power Query Exercises, https://powerqueryexercises.com/downloads/clinic-appointments-workbook)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
// Workbook the pack file appointments.xlsx -> File.Contents(PackFolder & "appointments.xlsx")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 18 rows with columns Site, ApptID, Date, Clinician, Type, DurationMin, Attended.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
PackFolder = "C:\PQE\clinic-appointments-workbook\",
Workbook = File.Contents(PackFolder & "appointments.xlsx"),
Source = Excel.Workbook(Workbook, true),
#"Site sheets" = Table.SelectRows(Source, each [Kind] = "Sheet" and not [Hidden]),
#"Site" = Table.RenameColumns(Table.SelectColumns(#"Site sheets", {"Name", "Data"}), {{"Name", "Site"}}),
#"Expanded" = Table.ExpandTableColumn(#"Site", "Data", {"ApptID", "Date", "Clinician", "Type", "DurationMin", "Attended"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded", {{"Date", type date}, {"DurationMin", Int64.Type}, {"Attended", type logical}})
in
#"Changed Type"
Attendance by site attendance-by-site.pq
Download .pqBooked, attended and no-show rate per site. Returns 2 rows: Site, Booked, Attended, NoShowRate.
// Attendance by site
// Pack: Clinic appointments workbook (Power Query Exercises, https://powerqueryexercises.com/downloads/clinic-appointments-workbook)
//
// Source bindings
// In the Power Query Exercises simulator each name below is bound directly to a pack file. In Excel or
// Power BI Desktop the first steps of this query do that binding instead:
// Workbook the pack file appointments.xlsx -> File.Contents(PackFolder & "appointments.xlsx")
// Set PackFolder to the folder where you extracted the pack (keep the trailing backslash).
//
// Validated in the Power Query Exercises simulator (engine 0.1.0) against this pack's files; it returns
// 2 rows with columns Site, Booked, Attended, NoShowRate.
// It has not been run in Excel or Power BI by us. This is query text, not a ready-to-refresh workbook.
// Independent practice material; not affiliated with or endorsed by Microsoft.
let
PackFolder = "C:\PQE\clinic-appointments-workbook\",
Workbook = File.Contents(PackFolder & "appointments.xlsx"),
Source = Excel.Workbook(Workbook, true),
#"Site sheets" = Table.SelectRows(Source, each [Kind] = "Sheet" and not [Hidden]),
#"Per site" = Table.AddColumn(#"Site sheets", "Stats", each [Booked = Table.RowCount([Data]), Attended = List.Count(List.Select([Data][Attended], (a) => a = true))]),
#"Expanded" = Table.ExpandRecordColumn(Table.SelectColumns(#"Per site", {"Name", "Stats"}), "Stats", {"Booked", "Attended"}),
#"No-show rate" = Table.AddColumn(#"Expanded", "NoShowRate", each Number.Round(1 - [Attended] / [Booked], 3), type number),
#"Renamed" = Table.RenameColumns(#"No-show rate", {{"Name", "Site"}})
in
#"Renamed"
Follow along in a host
Follow along in Excel
- Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
- In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
- In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
- Change the PackFolder line to the folder you extracted to, then choose Done.
- If Excel asks about privacy levels or file access, allow access to that folder. Then choose Home → Close & Load.
Follow along in Power BI Desktop
- Download the pack and extract the zip.
- In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
- Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
- Choose Done, check the preview against the expected rows in the query header, then Close & Apply.
Then try this
- Open appointments.xlsx, add a third sheet for a new site with the same headers, save and refresh: it is picked up without editing the query.
- Unhide Lookup notes (right-click a sheet tab → Unhide) and notice it still isn't imported.
Related lessons
Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.