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!

Sorry. Then I would probably add a conditional columnIF [column] = null then NULLELSE [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.

Replacing all non-null values | Microsoft Fabric Community

13 Replies

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
  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
    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"})
Anonymous
Not applicable
7 years ago
In the query editor,select your column. Then on the Transform tab on top choose Replace values and select "null" with "yes".
Repeat for every column
- tgjones43
  Helper IV
  7 years ago
  Thanks, but I want to leave the "null" values as "null". It is all the other values that I want to replace with "Yes". There are thousands of different values, all of which need to be "Yes".
  - Anonymous
    Not applicable
    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.
Anonymous
Not applicable
7 years ago
Hi tgjones43,
in Power Query you can add conditional columns :

Regards
Chiara
- tgjones43
  Helper IV
  7 years ago
  Thanks Anonymous and Anonymous. I thought that it wasn't possible to do this using the replace all function. I'd prefer to not have to create new columns as I have so many of them. I think there may be a way to avoid having to replace values if I use a different query in a previous step, so I will try to work that out.
  
  Thanks for your help.
  - vissvess
    Helper V
    7 years ago
    tgjones43 Hi..
    
    Just came accross the same scenario... I found out a way to do that...
    
    1. Select all columns you want to transform at once.
    2. Change data type to irrelevant type. say, if the column has only text, change it to whole number so that all non null values would be as "errors".
    3. Now, change the data type back to original text/whole number. (in your case to replace with 'yes', change it to text)
    4. Transform>Replace error>'Yes'.
    
    Thats it... Its done.. Hope it worked. Update how it worked...
rpride
New Member
2 years ago
Yes, you can do it with the Replace Values function!
In this table, column headed "F01" has empty cells that are not "blank"

Select the column or click into a cell within it. Now right-click, and select Replace Values.
Leave the top "Value to Find" box as is. In the lower box, type "null"
click "OK"
Now we have "null" in all the formerly blank cells! yay!