PQ #5 – Understanding the Applied Steps Pane: The Key to Data Transformation
POWER QUERY
Power Query for Finance Professionals: From Zero to Hero
Welcome back to the Power Query for Finance Professionals: From Zero to Hero series! This series is designed to take you from a complete beginner to a Power Query expert, helping you automate data preparation and improve efficiency in your financial workflows.
If you haven’t already, check out the previous post in this series – they are available in the Related Articles section at the end of this article.
Introduction: Why Understanding Applied Steps Matters
As a finance professional, you often need to prepare your data before using it. You load a dataset into Power Query, and you start cleaning it up: removing columns, renaming headers, changing data types. Then you ask yourself: How do I keep track of what I’ve done so far? What if I want to undo a step?
This is where the Applied Steps pane comes into play - a hidden gem that quietly records your every move.
In this guide, you’ll learn:
What the Applied Steps pane is and where to find it
How Power Query tracks every transformation
How to manage, rename, reorder, and delete steps
Why understanding Applied Steps gives you full control and confidence in your queries
By the end of this post, you’ll master the Applied Steps pane, allowing you to fully track the changes you make to your data.
The Problem: The Hidden Complexity of Manual Edits
Imagine you are working on a sales dataset. You:
✅ Remove some unnecessary columns
✅ Filter out blank rows
✅ Rename a few columns headers
✅ Change data types
You think you’re done - but then a teammate says, "Can we keep that column you removed?" or "What exact filters did you apply?
Without a proper record of your steps, you have to:
✅ Rerun the query manually
✅ Try to remember what you changed
✅ Even worse: you possibly have to start from scratch
This is inefficient, and worse, it opens the door to errors. Applied Steps solves this problem by automatically creating a chronological list of every transformation you make.
To explore the Applied Steps pane, you'll need:
✅ Excel 2016 or later (Power Query is built-in)
✅ A dataset loaded into Power Query. If you’re unsure how to load data into Power Query, refer to PQ #4: Importing Data Like a Pro
Navigating and Using the Applied Steps Pane
What is the Applied Steps Pane?
The Applied Steps pane appears on the right-hand side of the Power Query Editor. It shows a list of every action you perform, in the order you perform it. Each action is called a step.
Example steps:
Source - the original data source
Navigation - selecting a table or worksheet
Removed Columns - deleting columns
Removed Top Rows - deleting rows
Promoted Headers - promoting a row to a column header
Changed Type - amending the data type of a column
Filtered Rows - Filtering a value from a column
Each step builds on the one before it, creating a logical transformation chain.
How to use it
Review Your Steps - Each time you perform an action in Power Query, it gets added as a new step. You can click on any step to preview how the data looked at that moment.
Rename Steps for Clarity - By default, Power Query gives generic names (like "Renamed Columns"). You can right-click any step and select Rename to give it something more descriptive like "Renamed Date to Transaction Date".
Describe a Step - It's a good practice to include context and explanation to a step. This makes it easier to understand and follow your process if you need to revisit it later.
Reorder Steps (With Caution) - You can move steps up or down by dragging them, but be careful: changing the order of steps can lead to errors if later steps depend on earlier ones.
Delete a step - If you made a mistake or want to remove a step, simply right-click and choose Delete. Power Query will automatically update the preview to reflect the change.
Edit a Step's Code (Advanced) - Clicking the gear icon or formula bar (if enabled) lets you modify the M code behind each step. This is useful if you want to tweak a transformation without starting over.
The Applied Steps pane will list each of these actions. You can click back to any step to check your progress.
For a step-by-step visual walkthrough of the Applied Steps Pane, check out the YouTube video below.
The Outcome: Visibility and Flexibility
Understanding the Applied Steps pane gives you:
✅ Transparency – Know exactly what changes have been made and when
✅ Auditability – You can explain your transformations to teammates or auditors
✅ Reversibility – Undo, delete, or tweak steps without starting over
✅ Efficiency – Troubleshoot or adjust transformations with ease
Instead of a black box, your data transformation process becomes a clear, documented pipeline. This is especially important in finance, where traceability is key.
Conclusion: Master the Building Blocks of Transformation
In this post, we explored:
✅ What the Applied Steps pane is and why it matters
✅ How to navigate, manage, and edit steps
✅ How it helps you maintain clean, transparent, and auditable queries
If you want to build repeatable, reliable data workflows, mastering the Applied Steps pane is a must.
Next Steps: Cleaning Messy Data
In the next post, we’ll dive into practical techniques for tidying up raw data.
Until then, try exploring the Applied Steps pane in your own queries and see how powerful it can be! The demo file is available at https://github.com/versatilebieu/power_query/tree/main
