Operational spreadsheet consulting for small businesses
A collection of practical patterns for cleaning messy operational data using Power Query.
Many organisations rely on spreadsheets that combine exports from different systems, manual entries, and historical data structures.
Power Query provides a reliable way to transform that data into consistent, analysis-ready datasets.
This repository documents common cleaning patterns used in operational Excel workflows.
Typical problems seen in operational spreadsheets include:
• inconsistent column naming
• mixed data types in the same column
• blank rows or hidden separators
• duplicate records
• manual formatting affecting values
• inconsistent date formats
• text values with trailing spaces
• exported system tables with metadata rows
Power Query allows these issues to be resolved in a repeatable way.
Remove rows that contain no meaningful values.
Useful when exports include separator rows or blank lines.
Remove extra spaces and non-printable characters.
Common when data is copied between systems.
Ensure columns follow consistent naming conventions before transformations.
This prevents downstream errors when structures change.
Identify and remove duplicate rows when datasets contain repeated entries.
Explicitly define data types such as:
• date
• number
• text
This reduces transformation errors later.
Example: