Power QueryExercises

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 .pq

TempC 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 .pq

What 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

  1. Download the pack and extract the zip, for example to C:\PQE\<pack name>\.
  2. In Excel (Microsoft 365 or 2016+ on Windows), go to Data → Get Data → From Other Sources → Blank Query.
  3. In the Power Query Editor choose Home → Advanced Editor, select everything and paste the contents of the .pq file.
  4. Change the PackFolder line to the folder you extracted to, then choose Done.
  5. 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

  1. Download the pack and extract the zip.
  2. In Power BI Desktop choose Home → Transform data to open the Power Query Editor, then Home → New Source → Blank Query.
  3. Choose Home → Advanced Editor, paste the contents of the .pq file and change the PackFolder line to your folder.
  4. Choose Done, check the preview against the expected rows in the query header, then Close & Apply.

Then try this

  1. Load both queries. The second shows exactly which rows a plain Change Type would break.
  2. 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.