Replacing values in a column based on logic

[83dons]

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.

[BhavinVyas3003]

You can create a custom column with this formula,

if [YourColumn] = null then 0
else if [YourColumn] > 100 then 100
else [YourColumn]

[burakkaragoz · Accepted solution]

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:

1. Go to Power Query Editor
2. Select Add Column > Conditional Column (or use a custom column)
3. Use this logic:

   if [YourColumn] > 100 then 100 
   else if [YourColumn] = null then 0 
   else [YourColumn]

1. 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