Power Query: Replace 80% of Your Copy-Paste Work
Set it up once. Then never copy-paste between sheets again. A complete step-by-step guide for analysts who want reports that refresh automatically every week.
What Power Query actually does
Power Query is a data transformation engine built directly into Excel and Power BI. You describe the steps needed to clean and reshape your data — once. Excel records those steps. Every time you refresh, it re-runs the exact same steps against the current source data automatically. Critically: the original source data is never modified. Power Query works on a copy, so your source files remain intact regardless of what transformations you apply.
The four transformations that cover 80% of use cases
Combining multiple sheets or files into one table. Cleaning column headers (removing spaces, standardising capitalisation, removing symbols). Splitting or merging text columns (separating full names into first and last, combining date and time into a single timestamp). Unpivoting — converting wide data with one column per month into tall data with a Month column. Learn these four transformations and you can handle the data preparation work in most analyst roles.
Setting it up step by step
Data tab → Get Data → From File (or From Table/Range if your data is already in Excel). Select your source. Power Query opens the editor. Every transformation you apply is recorded in the Applied Steps panel on the right side. You can click any step to see what the data looked like at that point, rename steps for clarity, or delete a step if you made an error. When you close and load, the transformed data appears in your spreadsheet. The original source is untouched.
The refresh cycle and automation
When source data updates, go to Data → Refresh All. Power Query re-runs all recorded steps against the current source and updates your output table. For files stored in SharePoint or OneDrive for Business, you can configure Power BI or Excel Online to refresh automatically on a schedule — meaning the output updates without you opening the file at all.
The two most common mistakes beginners make
Mistake one: applying transformations directly to the source file instead of using Power Query — then the source is corrupted and there is no clean version to fall back on. Always keep source files read-only. Mistake two: building a query with a hardcoded local file path (C:\Users\YourName\Documents\...) and then moving the file, which breaks the connection immediately. Use a relative path or a parameter for the file location from the start.
This topic is covered in the Advanced Excel with AI course. Sessions are live, practical and taught with real business data — a natural next step if this article matches what you are working on.