Changing values above 1 to 0

[Anonymous]

Hi there! 

I have a table with one column where all values should be 1 or less than 1. However, there are quite a lot of values above that and I was wondering if there was a way I could change this easily.

[ahmedoye · Accepted solution]

You can change this in Power Query using the suggestion below: If it works for you, kindly mark this as solution so anyone with similar issues can easily find it.

Step 1: Rightclick on the column in Power Query and Select Replace Values
Step 2: In the replace values dialog box, replace 1 with 0. You will have something like the below
           Table.ReplaceValue(#"Changed Type",1,0,Replacer.ReplaceValue,{"Item"})
Step 3: From the above code, go into the PowerQuery formula bar and replace the 1 with the below
            (each if [Item] >1 then [Item] else 0). Then you will have something like the below:
Table.ReplaceValue(#"Changed Type",(each if [Item] >1 then [Item] else 0) ,0,Replacer.ReplaceValue,{"Item"})

[Anonymous]

That worked, thank you so much!

[vanessafvg]

you chould change the values to an if statement.

can you provide more information such as what does your table look like, what is the rule one needs to apply?  ie how yo you know which value to convert it to.