// Stock as a table // Pack: Warehouse report layout (Power Query Exercises, https://powerqueryexercises.com/downloads/warehouse-report-layout) // // 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: // StockReport the pack file stock-report.csv -> File.Contents(PackFolder & "stock-report.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 // 12 rows with columns Item, Bin, Qty, Location. // 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\warehouse-report-layout\", StockReport = File.Contents(PackFolder & "stock-report.csv"), Source = Table.PromoteHeaders(Csv.Document(StockReport, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]), #"Location" = Table.AddColumn(Source, "Location", each if Text.StartsWith([Item], "Location: ") then Text.AfterDelimiter([Item], ": ") else null, type text), #"Filled Down" = Table.FillDown(#"Location", {"Location"}), #"Items only" = Table.SelectRows(#"Filled Down", each [Item] <> "" and [Item] <> "Subtotal" and not Text.StartsWith([Item], "Location: ")), #"Changed Type" = Table.TransformColumnTypes(#"Items only", {{"Qty", Int64.Type}}) in #"Changed Type"