// Clean transactions // 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 // 24 rows with columns TxnID, Timestamp, Item, Category, Qty, UnitPrice, Staff, LineTotal. // 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 = Csv.Document(TillExport, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Timestamp", type datetime}, {"Qty", Int64.Type}, {"UnitPrice", type number}}, "en-US"), #"Paid only" = Table.SelectRows(#"Changed Type", each [Status] = "Paid"), #"Clean staff" = Table.TransformColumns(#"Paid only", {{"Staff", each Text.Proper(Text.Trim(_)), type text}}), #"Line total" = Table.AddColumn(#"Clean staff", "LineTotal", each [Qty] * [UnitPrice], type number), #"Removed Status" = Table.RemoveColumns(#"Line total", {"Status"}) in #"Removed Status"