// Combine every monthly file // Pack: Coffee roaster monthly files (Power Query Exercises, https://powerqueryexercises.com/downloads/roaster-monthly-folder) // // 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: // MonthlyFolder every file in the pack's monthly/ folder -> Folder.Files(PackFolder & "monthly") // 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 SourceFile, Date, Blend, Bags, PricePerBag, Origin. // 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\roaster-monthly-folder\", MonthlyFolder = Folder.Files(PackFolder & "monthly"), Source = Table.SelectRows(MonthlyFolder, each Text.Lower([Extension]) = ".csv"), #"Parsed" = Table.AddColumn(Source, "Data", each Table.PromoteHeaders(Csv.Document([Content]), [PromoteAllScalars=true])), #"Kept" = Table.RenameColumns(Table.SelectColumns(#"Parsed", {"Name", "Data"}), {{"Name", "SourceFile"}}), #"Columns" = List.Union(List.Transform(#"Kept"[Data], Table.ColumnNames)), #"Expanded" = Table.ExpandTableColumn(#"Kept", "Data", #"Columns"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded", {{"Date", type date}, {"Bags", Int64.Type}, {"PricePerBag", type number}}, "en-US") in #"Changed Type"