cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tgjones43 Member
Member

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

Accepted Solutions
adetogni Established Member
Established Member

Re: Replacing all non-null values

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.

8 REPLIES 8
adetogni Established Member
Established Member

Re: Replacing all non-null values

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 Member
Member

Re: Replacing all non-null values

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

adetogni Established Member
Established Member

Re: Replacing all non-null values

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.

Chiara Regular Visitor
Regular Visitor

Re: Replacing all non-null values

Hi @tgjones43,

in Power Query you can add conditional columns :

 

23.PNG

 

 

 

Regards 

Chiara

tgjones43 Member
Member

Re: Replacing all non-null values

Thanks @adetogni and @Chiara. 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.

adetogni Established Member
Established Member

Re: Replacing all non-null values

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

jschuchter Frequent Visitor
Frequent Visitor

Re: Replacing all non-null values

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

vissvess Member
Member

Re: Replacing all non-null values

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 279 members 2,615 guests
Please welcome our newest community members: