Amey-Thakur Power Query
Manual cleaning must be redone every time the data arrives. Power Query records the steps as a pipeline that reruns against new data, which is the difference between a one-off and a report.
Method
- Use it whenever the task repeats. Any monthly or weekly import justifies the setup immediately (see spreadsheet-data-cleaning).
- Keep the source connection parameterised. A file path or date range as a parameter rather than embedded, so refreshing a new period does not mean editing steps.
- Do transformations in the query, not in the sheet. Steps in the query are reproducible; formulas applied afterwards must be reapplied.
- Name each step for what it does. The step list is the documentation of the pipeline, and default names make it unreadable.
- Filter early in the pipeline. Reducing rows before expensive transformations makes refresh substantially faster.
- Handle schema changes defensively. Column renames and additions at the source break queries, so referencing by name with error handling beats positional assumptions (see data-mapping).
- Load to the data model for large sets. Loading millions of rows to a sheet is slow and unnecessary when only summaries are needed.
Boundaries
Power Query transforms on refresh and is not live. Very large or frequently changing data belongs in a database or a warehouse (see data-pipeline-design). Query logic is less visible than formulas, so undocumented queries become opaque to others.