// 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"