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