// Daily totals by category // Pack: Café till export (Power Query Exercises, https://powerqueryexercises.com/downloads/cafe-till-export) // // 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: // TillExport the pack file till-export.csv -> File.Contents(PackFolder & "till-export.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 // 14 rows with columns Day, Category, Items, Revenue. // 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\cafe-till-export\", TillExport = File.Contents(PackFolder & "till-export.csv"), Source = Table.PromoteHeaders(Csv.Document(TillExport, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(Source, {{"Timestamp", type datetime}, {"Qty", Int64.Type}, {"UnitPrice", type number}}, "en-US"), #"Paid only" = Table.SelectRows(#"Changed Type", each [Status] = "Paid"), #"Day" = Table.AddColumn(#"Paid only", "Day", each DateTime.Date([Timestamp]), type date), #"Grouped" = Table.Group(#"Day", {"Day", "Category"}, {{"Items", each List.Sum([Qty]), Int64.Type}, {"Revenue", each List.Sum(List.Transform(Table.ToRecords(_), (r) => r[Qty] * r[UnitPrice])), type number}}), #"Sorted" = Table.Sort(#"Grouped", {{"Day", Order.Ascending}, {"Category", Order.Ascending}}) in #"Sorted"