Use Power Query when the same import and cleanup steps repeat. Connect to the source, apply explicit transformation steps, load the result to a table or data model, and refresh when a new file arrives. Keep business calculations outside the query unless they are part of stable data preparation.
- Power Query records transformation steps instead of changing the source file.
- Set data types deliberately, especially for dates, identifiers, and decimal separators.
- Design folder imports around a stable file schema and handle unexpected columns visibly.
Think in repeatable transformation steps
A query can remove unused columns, split text, standardize types, filter rows, merge lookup data, and append files. The Applied Steps list is both the process and an audit trail. Rename steps when the default name does not explain the intent.
Power Query uses a preview while editing. A successful preview does not guarantee that every future file has the same columns and types, so refresh errors should be treated as useful data-quality signals.
Choose the right load destination
Load to a worksheet table when people need to inspect the rows directly. Use the data model for larger datasets and relationships across tables. A connection-only query is useful as an intermediate step feeding other queries.
- Keep source paths and parameters easy to update.
- Do not manually edit the loaded output, because refresh will replace it.
- Document the expected source columns and refresh owner.
Combine monthly files from one folder
Each monthly sales CSV uses the same columns and is saved in one controlled folder.
- Connect Power Query to the folder and choose Combine and Transform.
- Keep the required columns, set their types, and filter out blank records.
- Load the combined query to a table, add next month's file, and run Refresh All.
Common mistakes
- Manually editing query output and losing the changes on refresh.
- Relying on automatic data types for identifiers with leading zeros.
- Combining files whose schemas differ without an explicit exception process.
- Leaving source paths tied to one person's local desktop.
Try one
What happens to a manual edit made directly inside a Power Query output table after refresh?
The refresh can replace it. Changes should be made in the source or encoded as a query transformation.
Sources
- Microsoft Support: About Power Query in ExcelOfficial overview of connecting, transforming, combining, and loading data with Power Query.