⚡ Productivity
Excel Power Query M Cleanup Builder for Monthly CSV Exports: Folder Combine, Locale Aware Data Types, Unpivot, Lookup Merge, Error Row Quarantine, Folder Path Parameter, and a Refresh Checklist
Replace a monthly copy and paste routine with a refreshable Excel Power Query: combine every CSV in a folder through a custom file function, type dates and decimals with the right locale, unpivot wide week columns, merge a lookup table, quarantine error rows instead of hiding them, and get full M code plus load settings and a refresh checklist.
0Reviews
Prompt
Act as an Excel Power Query developer who writes M code by hand for finance and operations teams, works in Excel for Microsoft 365, and has fixed refreshes broken by renamed columns, a hardcoded column count, comma decimals, and the Formula.Firewall privacy error. Inputs: - Excel version and platform (Microsoft 365 Windows, Microsoft 365 Mac, Excel 2019): [ExcelVersion] - Header plus 3 rows from at least two of the CSV files, with file names, delimiter, and encoding if known: [FileSamples] - The table you want at the end, column by column: [TargetShape] - Any lookup table to merge, with its Excel table name and key column: [LookupTable] - Locale of the export versus the workbook (comma or dot decimals, date order): [Locale] - Output format: [Format] Generate: 1. A shape audit of FileSamples: each column, its raw values, delimiter, header row position, footer or total rows, and every difference between files such as an extra column in a longer month. 2. A FolderPath text parameter and a source step with Folder.Files that keeps only .csv files (lowercase the extension), skips temp files that start with ~$, and keeps Name and Content. 3. A custom function fnLoadFile using Csv.Document with explicit Delimiter, Encoding (65001 for UTF-8), and QuoteStyle.Csv but no Columns count, so a file with more columns is not cut off. Then Table.PromoteHeaders with PromoteAllScalars and a filter that removes total or blank rows. 4. A combine step that adds SourceFile to each table and uses Table.Combine, which aligns columns by name and fills missing ones with null. 5. Reshape to TargetShape: Table.UnpivotOtherColumns for wide period columns, noting that unpivot drops null cells, and a derived period column parsed from SourceFile. 6. Types with culture: Table.TransformColumnTypes with the export Locale as the culture argument, placed after unpivot, and never the auto generated Changed Type step. 7. A lookup merge with Table.NestedJoin and JoinKind.LeftOuter, expanding only needed columns, plus a check query that lists keys with no match. 8. Error quarantine: a staging query, a clean query using Table.RemoveRowsWithErrors, and an errors query using Table.SelectRowsWithErrors loaded to a QA sheet. 9. Load settings for each query, the privacy level fix for Formula.Firewall, and a monthly refresh checklist. Constraints: - Only use functions available in ExcelVersion and flag anything that differs on Mac. - Never invent a column that is not in FileSamples. No em dashes.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
ExcelVersion: Microsoft 365 for Windows, current channel FileSamples: Sales_2026-07.csv (UTF-8, semicolon) Store;SKU;Unit Price;W1;W2;W3;W4 Leeds;SKU-1042;13,95;12;9;14;11 York;SKU-1042;13,95;7;8;6;10 Total;;;19;17;20;21 Sales_2026-08.csv has the same header plus W5 TargetShape: Month, Store, SKU, Product, Category, Week, Qty, Unit Price, Revenue, SourceFile LookupTable: Excel table tblSKU on sheet Lists with SKU, Product, Category Locale: export from a German POS with comma decimals; workbook is en-GB Format: audit, M code per query, load settings, refresh checklist
Example Output
Shape audit
- Delimiter semicolon, UTF-8, header on row 1, a Total footer row to drop.
- Unit Price uses comma decimals (13,95), so type it with culture de-DE.
- July has W1 to W4, August adds W5. A fixed Columns = 7 setting would cut W5, so fnLoadFile sets no column count.
Parameter FolderPath (Text): C:\Reports\POS\Monthly
fnLoadFile
(FileContent as binary) as table =>
let
Source = Csv.Document(FileContent, [Delimiter = ";", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
NoTotals = Table.SelectRows(Promoted, each [Store] <> "Total" and [Store] <> "")
in
NoTotals
tblSKU
let
Source = Excel.CurrentWorkbook(){[Name = "tblSKU"]}[Content]
in
Source
Sales_Staging
let
Files = Folder.Files(FolderPath),
CsvOnly = Table.SelectRows(Files, each Text.Lower([Extension]) = ".csv" and not Text.StartsWith([Name], "~$")),
Loaded = Table.AddColumn(CsvOnly, "Data", (f) => Table.AddColumn(fnLoadFile(f[Content]), "SourceFile", each f[Name])),
Combined = Table.Combine(Loaded[Data]),
Unpivoted = Table.UnpivotOtherColumns(Combined, {"SourceFile", "Store", "SKU", "Unit Price"}, "Week", "Qty"),
Typed = Table.TransformColumnTypes(Unpivoted, {{"Unit Price", type number}, {"Qty", Int64.Type}}, "de-DE"),
WithMonth = Table.AddColumn(Typed, "Month", each Date.FromText(Text.Middle([SourceFile], 6, 7) & "-01"), type date),
Merged = Table.NestedJoin(WithMonth, {"SKU"}, tblSKU, {"SKU"}, "Lookup", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Lookup", {"Product", "Category"}),
Revenue = Table.AddColumn(Expanded, "Revenue", each [Qty] * [Unit Price], type number),
Ordered = Table.SelectColumns(Revenue, {"Month", "Store", "SKU", "Product", "Category", "Week", "Qty", "Unit Price", "Revenue", "SourceFile"})
in
Ordered
Notes on Sales_Staging
- Table.Combine aligns by column name, so July rows get null in W5. Unpivot drops those null cells, so July has no empty W5 rows.
- Text.Middle([SourceFile], 6, 7) reads 2026-07 from Sales_2026-07.csv. If the file naming changes, this step errors and the rows land in Sales_Errors.
Sales
let
Source = Table.RemoveRowsWithErrors(Sales_Staging)
in
Source
Sales_Errors
let
Source = Table.SelectRowsWithErrors(Sales_Staging)
in
Source
Check_Unmatched_SKU
let
Source = Table.Distinct(Table.SelectColumns(Table.SelectRows(Sales, each [Product] = null), {"SKU"}))
in
Source
Load settings
- fnLoadFile, FolderPath, tblSKU, Sales_Staging: Connection only.
- Sales: table on sheet Data, or Data Model if a PivotTable reads it.
- Sales_Errors and Check_Unmatched_SKU: tables on sheet QA.
Formula.Firewall fix
Folder.Files and Excel.CurrentWorkbook are separate sources. If refresh says a query references other queries, set both to Organizational under Data > Get Data > Query Options > Privacy.
Monthly refresh checklist
1. Save the new CSV into the folder with the Sales_YYYY-MM.csv name.
2. Data > Refresh All.
3. QA sheet: Sales_Errors and Check_Unmatched_SKU should be empty. Add missing SKUs to tblSKU and refresh again.
4. Spot check one store and week against the raw CSV.
On Mac, Power Query in Microsoft 365 supports these steps; confirm the parameter dialog in your build.