Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tgjones43
Helper IV
Helper IV

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

13 REPLIES 13
rpride
New Member

Yes, you can do it with the Replace Values function!

In this table, column headed "F01" has empty cells that are not "blank"

rpride_0-1703863851452.png

 

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" 

rpride_1-1703863956422.png

click "OK"

Now we have "null"  in all the formerly blank cells! yay! 

rpride_2-1703864123613.png

 

 

 

 

Exceloholic
Regular Visitor

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

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 

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

Hi @tgjones43,

in Power Query you can add conditional columns :

 

23.PNG

 

 

 

Regards 

Chiara

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.

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

Thank you @vissvess you have a great solution.  In my case I had survey results with multiple columns for one question.  

bdub1976_0-1599086130306.png

I needed the nulls to be 0 and anything with text to be 1.  Taking his advice I converted the text column to whole number, replaced nulls with 0, and then replaced errors with 1.  Viola!

bdub1976_1-1599086243828.png

 

Anonymous
Not applicable

Given that M does not have a "replace if different than"

https://docs.microsoft.com/en-us/powerquery-m/replacer-replacevalue

I don't believe you have options.

 

Unless of course you do it before importing data

In Excel you can use the wildcard "*" to identify non null cells and replace. But this doesn't work in BI

Anonymous
Not applicable

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

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.