Replace All that are Not a Value

[aidang]

With the 'Replace Values' function, there should be a way to change the values if they are not a specified value. Right now, you can only do it if it is a specified value, but it would help if I could change the values if they are not equal to something. For instance, I have a number column and I want to make it a binary field. If the value is zero, it should stay zero. If it is not zero, I want to change it to 1. It appears that this can only be done if a new column is added, which I would like to avoid to keep the file size smaller. Instead I would like to change the value in that column to 1 if the current is not zero

[kleigh]

This is possible, but it's not obvious: Table.ReplaceValue(#"Changed Type",each [Column1], each if [Column1] = 0 then 0 else 1,Replacer.ReplaceValue,{"Column1"}) ... The trick is the oldValue and newValue parameters can be dynamic. Like many capabilities in Power Query it's not exposed by the UI. See later examples of https://learn.microsoft.com/en-us/powerquery-m/table-replacevalue or plenty at https://gorilla.bi/power-query/replace-values/ . It would help if the UI could support this, possibly with some templates for common cases.