Power BI – Applied Steps Replace Current or Add New

By: David Rohlfs

Introduction

Especially when you are new to Power BI and Power Query there are a lot of little things that you just don’t know what to do with. A very common question that people have is whether it is better to add a new step or replace the current step when they get the pop-out in Power Query.

In this blog we will go over what it means to replace a step or add a new step to your applied steps in Power Query.

What is it?

What is this message for?

You are being asked whether to add a new step or replace the current because of how the code is built behind Power Query. The way that M Code (the code that runs ETL on a query) works is that there is a list of steps that the query is run through. If you have a query that you changed a type on Column A then you go to change type on Column B directly after, Power Query asks you whether to combine the two steps or to keep them separate. This is what the message to replace or add new is really asking.

When you choose to Replace Current, you are choosing to combine the previous and current step together. If the transformations that you made are meant to be permanent it is beneficial to choose the replace current option. The main benefit is that you will have a better performance on your query. If you make changes to this step later on it can be a bit more difficult, but with simple transformations it tends to be simple enough.

When you choose to Add New you are choosing to create a new step that the query needs to run. In most cases you will only do this if you plan to delete this step in the future. This is most common when you are verifying data and either want to move the step or only need it for an intermittent check. I say that you wouldn’t want to do this if the step is permanent because creating a second step that could be combined into a single step can have an impact on performance.

Now that we have gone over the basics, let’s look at simple examples of the two outcomes.

On the left is a query that has had the Replace Current option chosen and the right is when we selected Add New. Notice that the Replace Current option has one less step to run.

Generally, when there are less steps to run your performance will increase.

So, what if I want to combine steps that are the same function, but they don’t give me the option to replace current or add new?

As you grow in your Power Query experience this question should be a recurring question when finalizing your queries. The basic answer is that many functions allow you to manually combine steps together. I recommend that you read through M Code Basics (blog linked below) because it will give you the introduction into reading and writing M Code in Power Query.

Here is an example where I combined the two steps into a single step. This will help with query performance and can also be easier to read later on by you or another developer.

Conclusion

I hope that this blog helped explain what it means to replace current or add new when you are in Power Query. There is a lot more behind the simple pop-out, so I recommend reading through the M Code Basics blog because it can help you advance your knowledge in Power Query. There will also be a couple of other related blogs linked below that you should check out.

Links Related to This Blog:

M Code Basics

Basic Relationship Building

Key Columns

Comments