Fridge sensor log
Half-hourly fridge temperatures with ERR codes, gaps and a -999 sentinel. Classify every reading instead of letting errors break the refresh.
intermediate · Skills: try … otherwise, Errors versus nulls, Keeping an error log · Updated 2026-11-04
Data files
- sensor-log.csv — Timestamp, Sensor, Reading (°C as text; ERR, blank and -999 appear).Download CSV (704 B)
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.
Classified readings classified-readings.pq
Download .pqTempC as a number where valid, plus a Status for every row. Returns 20 rows: Timestamp, Sensor, Reading, TempC, Status.
// Classified readings
// Pack: Fridge sensor log (Power Query Exercises, https://powerqueryexercises.com/downloads/sensor-log-errors)
//
// 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:
// SensorLog the pack file sensor-log.csv -> File.Contents(PackFolder & "sensor-log.csv")
// 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
// 20 rows with columns Timestamp, Sensor, Reading, TempC, Status.
// 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\sensor-log-errors\",
SensorLog = File.Contents(PackFolder & "sensor-log.csv"),
Source = Table.PromoteHeaders(Csv.Document(SensorLog, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]),
#"Parsed" = Table.AddColumn(Source, "TempC", each try Number.FromText([Reading], "en-US") otherwise null, type number),
#"Status" = Table.AddColumn(#"Parsed", "Status", each if [Reading] = "" then "missing" else if [TempC] = null then "invalid" else if [TempC] = -999 then "sentinel" else "ok", type text),
#"Clean value" = Table.TransformColumns(#"Status", {{"TempC", each if _ = -999 then null else _, type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Clean value", {{"Timestamp", type datetime}})
in
#"Changed Type"
Rows that fail a plain type change conversion-errors.pq
Download .pqWhat a naive Change Type would turn into errors. Returns 2 rows: Timestamp, Sensor, Reading.
// Rows that fail a plain type change
// Pack: Fridge sensor log (Power Query Exercises, https://powerqueryexercises.com/downloads/sensor-log-errors)
//
// 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:
// SensorLog the pack file sensor-log.csv -> File.Contents(PackFolder & "sensor-log.csv")
// 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 Timestamp, Sensor, Reading.
// 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\sensor-log-errors\",
SensorLog = File.Contents(PackFolder & "sensor-log.csv"),
Source = Table.PromoteHeaders(Csv.Document(SensorLog, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Reading", type number}}, "en-US"),
#"Errors only" = Table.SelectRowsWithErrors(#"Changed Type", {"Reading"})
in
#"Errors only"
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
- Load both queries. The second shows exactly which rows a plain Change Type would break.
- Notice that the blank readings become null, not errors, when typed: the CSV cell is empty text.
Related lessons
Original fictional data, free to use for learning and teaching. Power Query Exercises is not affiliated with or endorsed by Microsoft.