// Usage between readings // Pack: Meter readings to usage (Power Query Exercises, https://powerqueryexercises.com/downloads/meter-readings-usage) // // 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: // Readings the pack file meter-readings.csv -> File.Contents(PackFolder & "meter-readings.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 // 18 rows with columns Meter, ReadOn, Reading, Seq, Usage. // 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\meter-readings-usage\", Readings = File.Contents(PackFolder & "meter-readings.csv"), Source = Table.TransformColumnTypes(Table.PromoteHeaders(Csv.Document(Readings, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])), {{"ReadOn", type date}, {"Reading", Int64.Type}}, "en-US"), #"Per meter" = Table.Group(Source, {"Meter"}, {{"Rows", each Table.AddIndexColumn(Table.Sort(_, {{"ReadOn", Order.Ascending}}), "Seq", 1, 1), type table}}), #"Usage" = Table.AddColumn(#"Per meter", "WithUsage", each let t = [Rows], prev = Table.RenameColumns(Table.SelectColumns(Table.TransformColumns(t, {{"Seq", (n) => n + 1}}), {"Seq", "Reading"}), {{"Reading", "Previous"}}), j = Table.ExpandTableColumn(Table.NestedJoin(t, {"Seq"}, prev, {"Seq"}, "P", JoinKind.LeftOuter), "P", {"Previous"}) in Table.AddColumn(j, "Usage", (r) => if r[Previous] = null then null else r[Reading] - r[Previous])), #"Combined" = Table.Combine(#"Usage"[WithUsage]), #"Sorted" = Table.Sort(Table.RemoveColumns(#"Combined", {"Previous"}), {{"Meter", Order.Ascending}, {"ReadOn", Order.Ascending}}) in #"Sorted"