How to Automate Recurring CSV Cleanup with Excel Power Query
Power Query is useful when the same kind of CSV arrives repeatedly and the cleanup steps should be repeatable. Build the query against a representative copy, keep the source file unchanged, inspect the automatically created type steps, and validate the refreshed output before replacing a manual process.
Start with a stable source and a copy
Keep one untouched CSV as the reference sample. In Excel, use Data > Get Data > From Text/CSV, preview the delimiter and encoding, and choose Transform Data when cleanup is needed. Avoid editing the original CSV to make the first import succeed; otherwise the query may depend on a one-off fix that will not exist in next week's file.
Build cleanup steps in an order you can explain
- Confirm the header row and promote headers if needed.
- Remove genuinely unused columns.
- Rename fields only when the downstream workbook needs stable names.
- Trim or clean text where the business meaning is unchanged.
- Handle blanks and known sentinel values deliberately.
- Set data types after you understand the values in each column.
- Filter rows only when you can explain why they should be excluded.
Give important steps descriptive names in the Applied Steps pane. A sequence called “Removed Columns2, Changed Type3, Filtered Rows4” is much harder to repair than steps named for the intended transformation.
Inspect automatic data-type detection
Power Query can infer types from a sample of the data. Microsoft's current documentation notes that type inference may choose the wrong type when later rows differ from the sampled rows. A column that looks numeric early in the file may contain codes such as NA later and fail when converted to a whole number.
For identifiers, account numbers, ZIP codes, or values with leading zeros, consider keeping the column as text. For real dates and numbers, inspect several rows—including unusual values—before committing to the type.
Preserve original values during risky transformations
When splitting, parsing, or converting a column could lose information, duplicate the column first and transform the copy. Microsoft's error guidance recommends examining the exact error reason, message, and detail. Keeping an original column makes it easier to compare the failed value instead of guessing what was changed.
Diagnose a refresh that suddenly breaks
A column was renamed or removed
A step-level error can occur when an Applied Step references a column that no longer exists. Start at the first failing step, compare the current CSV headers with the sample that built the query, and decide whether the source changed intentionally. Fix the earliest broken assumption rather than deleting later steps at random.
A value cannot be converted
Filter or inspect the error rows. Determine whether the source value is invalid, represents a legitimate exception such as NA, or indicates that the chosen type is wrong. Do not replace every error with zero unless zero has the correct business meaning.
The delimiter or encoding changed
If the entire preview shifts into one column or characters become corrupted, return to the Source step and verify the current file's delimiter and encoding. A query cannot reliably clean fields that were parsed into the wrong columns.
Keep schema drift visible
Recurring files are safest when the producer keeps stable headers and field meaning. If new columns can appear, decide whether they should be intentionally ignored or incorporated. If required columns disappear, fail visibly rather than silently producing an incomplete report.
Validate refresh before relying on it
- Compare row counts with the source.
- Check several records from the beginning, middle, and end.
- Confirm totals or other reconciliation numbers that matter to the workflow.
- Inspect any query errors before publishing the output.
- Refresh using a second representative CSV before declaring the process reusable.
If the recurring files live in one folder and share the same structure, How to Combine Files from a Folder with Excel Power Query covers the folder-combine pattern. If the source contains text that needs pattern cleanup, see How to Clean Messy Spreadsheet Data with Google Sheets Regex for a separate Sheets workflow.