Forum Discussion
Helper IIIReplacing values in a column based on logic
I have a column with values between 0-100
However the data feeding this seems to have values over 100 such 101 and 131 and some nulls etc.
I have added a simple 'Replace Value' for nulls with 0. How do I replace any value 101 and over with 100? The Replace Value doesnt seem to allow such logic.
Hi 83dons ,
You’re right — the standard Replace Values option in Power Query doesn’t support conditional logic like “replace if greater than 100.”
Here’s how you can do it:
- Go to Power Query Editor
- Select Add Column > Conditional Column (or use a custom column)
- Use this logic:
if [YourColumn] > 100 then 100 else if [YourColumn] = null then 0 else [YourColumn]
- If you want to overwrite the original column, you can use Transform > Replace Values with a Custom Column like:
Table.AddColumn(#"Previous Step", "NewColumn", each if [YourColumn] > 100 then 100 else if [YourColumn] = null then 0 else [YourColumn])
Then just remove the original column and rename the new one.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
2 Replies
- burakkaragoz
Super User
Hi 83dons ,
You’re right — the standard Replace Values option in Power Query doesn’t support conditional logic like “replace if greater than 100.”
Here’s how you can do it:
- Go to Power Query Editor
- Select Add Column > Conditional Column (or use a custom column)
- Use this logic:
if [YourColumn] > 100 then 100 else if [YourColumn] = null then 0 else [YourColumn]
- If you want to overwrite the original column, you can use Transform > Replace Values with a Custom Column like:
Table.AddColumn(#"Previous Step", "NewColumn", each if [YourColumn] > 100 then 100 else if [YourColumn] = null then 0 else [YourColumn])
Then just remove the original column and rename the new one.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI - BhavinVyas3003
You can create a custom column with this formula,
if [YourColumn] = null then 0
else if [YourColumn] > 100 then 100
else [YourColumn]
