Override Values With Formula Using Power Query
If you haven’t learned about Excel’s Power Query feature, you are missing out on a huge opportunity to create automated solutions for your data cleansing and consolidation.
It is highly likely while utilizing Excel’s Power Query to manipulate your data that you will run into instances where you would like to override values within an existing column based on some sort of logical test (such as an IF statement). While this is possible within Power Query, it is not very apparent since there is not a button in the Ribbon that allows you to create a formula to override current data.
Let’s walk through an example to see how we can modify some pre-generated M Language code to achieve our desired result.
Example: Overriding Bonus Column
The example data we’ll be using in the article example displays various employees who are eligible for an annual bonus. As you will notice there are different bonus programs that are either based on a percentage of salary or a provided static amount.
What we will be trying to do is override the Bonus column to calculate everyone’s bonus based on a percentage of Annual Salary. This will ensure that the data within the Bonus column is not a mixture of percentages and amounts…
Creating the Query
Convert your data range into an Excel Table by one of the following methods
- Keyboard shortcut: ctrl + t
- Excel Ribbon: Insert Tab >> Table button
Click a cell inside your table and query the Table using Power Query
- Excel Ribbon: Data tab >> Get & Transform Data group >> From Table/Range button
Create A Replace Values Command
If possible, I always like to try and have Power Query give me some starting code that I can modify if I’m needing to write custom code. Since Power Query already has a Replace Values feature, let’s start by right-clicking on the column we wish to modify and select Replace Values.
You should see a window pop-up asking for your Find/Replace values. You can quickly input two random values since we will be overriding these very soon.
You should see a formula written similar to what is shown above, assuming you inserted this new Step after your auto-generated “Change Type” step. Now we have a good starting point to manipulate this formula to perform our desired Find/Replace based on specific criteria.
Writing A Custom Replace Value Formula
Before we go through and modify the ReplaceValue function. It might be helpful to understand its inputs. The ReplaceValue function has the following 5 inputs:
Table.ReplaceValue- table (as a table)
- oldValue (as any)
- newValue (as any)
- replacer (as a function)
- columnsToSearch (as list)
SOLUTION: Here is the custom M Language code we will be utilizing to accomplish our desired output:
= Table.ReplaceValue(#"Changed Type",each [Bonus] , each if [Program Format] = "Amount" then [Bonus]/[Annual Salary] else if [Program Format] = "Percentage" then [Bonus] / 100 else null, Replacer.ReplaceValue,{"Bonus"})
Keep reading on as I describe what I used the values shown above for each of the inputs in the ReplaceValue function.
Table: # “Changed Type”
This references the state of the table you would like to perform this function on. In most cases, this will be the name of the prior step in your Applied Steps list. In this case, I am applying this find/replace step after a step named “Changed Type”.
OldValue: Each [Bonus]
This part of the function is the “Old Value”. By starting with the word “each”, you indicate that you would like to replace each cell within the [Bonus] column (note: brackets indicate the reference of a table column).
NewValue: Each If…..
Now to the fun part. Again, we will start with the word “each” to indicate we want this formula applied to every cell. We’ll next need to proceed with our logical formula. In this case, we’ll utilize an IF/THEN formula to produce our desired result. The logic reads like this:
IF [Program Format] = "Amount" THEN
[Bonus] / [Annual Salary]
ELSE IF [Program Format] = "Percentage" THEN
[Bonus] / 100
ELSE
nullReplacer: ReplaceValue
Indicate what type of data you want to target (ie ReplaceValue or ReplaceText)
ColumnsToSearch: {“Bonus”}
This is the column (or list of columns) that we are going to perform the find/replace within.
A Little Bit of Clean Up
Now that we have our desired numbers calculated in the Bonus column, let’s add a few more steps to clean up the data table a bit.
Rename Column
I rename my Bonus column to read “Bonus %” since all the values are not in percentages. Here is the M language code for this step:
= Table.RenameColumns(#"Calculate Bonus %",{{"Bonus", "Bonus %"}})Format Bonus % Column
Now that all the values in the Bonus column are percentages, it makes sense to format the entire column as a percentage type. Here is the M language code for this step:
= Table.TransformColumnTypes(#"Renamed Columns",{{"Bonus %", Percentage.Type}})Remove Column
Finally, we’ll remove the Program Format column, since it is not necessary to include it anymore as all the bonuses have been converted into percentages. Here is the M language code for this step:
= Table.RemoveColumns(#"Changed Type1",{"Program Format"})End Result
The end result after the query has been refreshed is shown below. This data is much easier to report on as the bonus information is now displayed using the same metric for every employee.
I Hope This Helped!
Hopefully, I was able to explain how you can use some custom code in Power Query to modify values in the desired column. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.