Forum Discussion

tgjones43's avatar
tgjones43
Helper IV
7 years ago
Solved

Replacing all non-null values

This is probably a fairly simple query, but I cannot work out how to do it.   How can I replace all values that are not null across multiple columns with the word Yes.   Thank you!
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Sorry.

     

    Then I would probably add a conditional column
    IF [column] = null then NULL

    ELSE [column]

     

    This calculated column will have your cleaned up values. Then you can hide the original one.

     

    As far as I know the "Replace value" function does not have what you need.

Exceloholic's avatar
Exceloholic
Regular Visitor
6 years ago

tgjones43  try this:

= Table.ReplaceValue(Source, each [Column], each if [Column] = null then null else "Yes", Replacer.ReplaceValue, {"Column"})

Fragment each [Column] in second parameter is taking current content of field in each row. 

poweruser55's avatar
poweruser55
Helper IV
4 years ago

How can I chnage this formula so that it takes a product and if the product is A B or C then the name is replaced with other and if false the product remains the same Exceloholic 

  • Exceloholic's avatar
    Exceloholic
    Regular Visitor
    3 years ago

    When you want to work within the same column, then it is still fine to use Table.ReplaceValue. That option has its limitations - you cannot to refer to values in other columns. You only can make conditions based on values in column being replaced.

    Anyways, here's the scheme:

     

     

    = Table.ReplaceValue(Source, each [Product], each if List.Contains({"a","b","c"}, [Product]) then "X" else [Product], Replacer.ReplaceValue, {"Product"})