How to Use the Excel Power Query M Cleanup Prompt to Turn a Folder of Monthly CSVs Into One Refreshable Table

Many teams still rebuild the same report every month: download the CSV, paste it under last month, fix the decimals, delete the totals row, and redo the lookups. Power Query can do all of that on refresh, but the code the editor writes for you breaks easily. It fixes the column count, guesses data types in the wrong locale, and hides error rows. The 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 prompt writes the M code by hand the way an experienced developer does, so next month is one click.

What the prompt produces

1. A shape audit of your sample files, including delimiters, totals rows, comma decimals, and differences between months.
2. A FolderPath parameter and a source step that keeps only CSV files and skips Excel temp files.
3. A custom file function that reads each CSV with an explicit delimiter and encoding, but no fixed column count.
4. A combine step with Table.Combine, which lines columns up by name and fills gaps with null.
5. A reshape step that unpivots wide period columns and parses the month from the file name.
6. Locale aware typing with the export locale passed as the culture.
7. A lookup merge plus a check query listing keys that did not match.
8. Error quarantine: a clean query and an errors query, so bad rows are visible.
9. Load settings, the Formula.Firewall fix, and a refresh checklist.

How to fill the inputs

ExcelVersion matters because function support and dialogs differ between Microsoft 365 on Windows, on Mac, and older perpetual versions.

FileSamples is the most important input. Paste the header and three rows from at least two files, with file names. If one month has an extra column, include that file.

TargetShape lists the columns you want at the end, in order.

LookupTable names the Excel table you merge against and its key column.

Locale describes where the export comes from. A German or French system writes 13,95, which an English workbook will misread without help.

Reading the example output

The example combines monthly point of sale exports with semicolons and comma decimals:

• The audit spots the trap. July has four week columns and August has five. A fixed column count of seven would quietly cut off week five, so the function sets none.
• fnLoadFile is short and readable: Csv.Document, PromoteHeaders, and a filter that drops the Total row.
• SourceFile travels with each row, added inside the function call, so the month can be parsed later and every row can be traced back to its file.
• Unpivot runs before typing. The output notes that unpivot drops null cells, so July does not produce empty week five rows.
• Typing uses de-DE, so 13,95 becomes 13.95 instead of an error or 1395.
• Errors are split out. Sales removes error rows, while Sales_Errors keeps them for a QA sheet, and Check_Unmatched_SKU lists products missing from the lookup.
• The Formula.Firewall note explains how to set privacy levels when a folder query and a workbook table are combined.

Tips for better results

• Rename steps in plain words. The prompt does this, and it makes the code easy to review next year.
• Keep raw files untouched in the folder. Do all cleanup in Power Query.
• Test with a deliberately broken file, such as a renamed column, and confirm the row lands in the errors query.
• If a PivotTable reads the result, load to the Data Model and keep helpers as connection only.

Mistakes to avoid

• Do not keep the automatic Changed Type step. It types before you reshape and uses the workbook locale.
• Do not hardcode the folder path inside the query. Use the parameter.
• Do not remove error rows without a place to see them.
• Do not change the file naming pattern without updating the step that parses the month.

Who it is for

Finance and operations analysts who rebuild a monthly report by hand, small business owners consolidating exports from a POS or bank, consultants handing off a refreshable workbook to a client, and Excel trainers teaching Power Query beyond the button clicks.

Related PromptDig links

Open the 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 prompt and paste your file samples. For more spreadsheet and workflow prompts, Browse more prompts. If you have an automation prompt that saves you hours, Share a prompt.