#excel automation

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.

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.