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