// Style, colour, size and pack // Pack: Product code parsing (Power Query Exercises, https://powerqueryexercises.com/downloads/product-code-parsing) // // 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: // Catalog the pack file catalog.csv -> File.Contents(PackFolder & "catalog.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 // 10 rows with columns Style, Colour, Size, Description, Price, PackOf. // 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\product-code-parsing\", Catalog = File.Contents(PackFolder & "catalog.csv"), Source = Table.PromoteHeaders(Csv.Document(Catalog, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]), #"Split" = Table.SplitColumn(Source, "Code", Splitter.SplitTextByDelimiter("-"), {"Style", "Colour", "Size"}), #"Pack" = Table.AddColumn(#"Split", "PackOf", each if Text.Contains([Description], "pack of ") then Number.From(Text.AfterDelimiter([Description], "pack of ")) else 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Pack", {{"Price", type number}}) in #"Changed Type"