cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thomaskelly Regular Visitor
Regular Visitor

Replace null values with contents from another column

Good Morning

 

Is it possible in query editor to replace the null values of one column with the adjcent contents of another column?

 

Many Thanks

 

Tom

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Replace null values with contents from another column

Yes. Select Add Column and then write an if statement something like this (it is case sensitive)

 

= if [column 1] = null then [column 2] else [column 1] 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Highlighted
bdymit Regular Visitor
Regular Visitor

Re: Replace null values with contents from another column

Saw this old post and found another solution, so I thought I would share.

 

You can modify the "M" code if you are not looking to add a column/ delete the old one.

 

=Table.ReplaceValue(#"Last Step",null, each _[Values Column],Replacer.ReplaceValue,{"Null Column"})

 

#"Last Step" being the previous step in your query

[Values Column] being the column that has the values in it to replace the nulls

"Null Column" being the column with the null values

 

Be sure to use the " each _[Values Column]" syntax with the spaces before and after "each", otherwise you will get an error.

 

Here is the original video from Miguel Escobar.

https://www.poweredsolutions.co/2015/05/08/power-query-for-excel-replace-values-using-values-from-an...

 

Cheers!

 

**NOTE: When I have used this, it changed all the data types in my query to "Any". I asked Miguel, and he reached out to MS to see if it is a bug or if it is intentional. If you are using it early in your query before you change your data types, might still be useful. Otherwise you can change your data types back. Just a fair warning!

View solution in original post

9 REPLIES 9

Re: Replace null values with contents from another column

Yes. Select Add Column and then write an if statement something like this (it is case sensitive)

 

= if [column 1] = null then [column 2] else [column 1] 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

sivapratap Member
Member

Re: Replace null values with contents from another column

hi everyone,

 

i have a problem with replacing null values with content from other column. The scenerio is something like this. let say i have three columns A,B,C

ABC
X1010
X.1null10
X.2null10
Y1111
Y.1null11
Y.2null11

 

C is my custom column created based on data from two columns A and B. please help with the query to construct above scenario.

 

Thanks,

Sivaaprataap.

sivaaprataap
Highlighted
bdymit Regular Visitor
Regular Visitor

Re: Replace null values with contents from another column

Saw this old post and found another solution, so I thought I would share.

 

You can modify the "M" code if you are not looking to add a column/ delete the old one.

 

=Table.ReplaceValue(#"Last Step",null, each _[Values Column],Replacer.ReplaceValue,{"Null Column"})

 

#"Last Step" being the previous step in your query

[Values Column] being the column that has the values in it to replace the nulls

"Null Column" being the column with the null values

 

Be sure to use the " each _[Values Column]" syntax with the spaces before and after "each", otherwise you will get an error.

 

Here is the original video from Miguel Escobar.

https://www.poweredsolutions.co/2015/05/08/power-query-for-excel-replace-values-using-values-from-an...

 

Cheers!

 

**NOTE: When I have used this, it changed all the data types in my query to "Any". I asked Miguel, and he reached out to MS to see if it is a bug or if it is intentional. If you are using it early in your query before you change your data types, might still be useful. Otherwise you can change your data types back. Just a fair warning!

View solution in original post

omrdmr Regular Visitor
Regular Visitor

Re: Replace null values with contents from another column

While @bdymit's  script works very well, data type change is clearly off-putting here. It looks like a bug. I would only expect Power query to change the data type of the field which we replace the nulls at, only when replaced values don't fit the data type of the new field. Otherwise why change all field data types?

bdymit Regular Visitor
Regular Visitor

Re: Replace null values with contents from another column

@omrdmr I agree, the data type change is annoying. Miguel responded to my question (in the post I linked to above) and he said that Microsoft changes the data types by design, it is not a bug. He gave a work-around, but I have yet to see if using the custom M code and then changing all the data types has query performance advantages over creating a conditional column to solve the issue.

DaveRuijter
Advisor

Re: Replace null values with contents from another column

FYI, it seems the data type change bug is no there, I'm not having any issues with that currently.

PFlorenzano Regular Visitor
Regular Visitor

Re: Replace null values with contents from another column

Thank you for the input!  So quick question, only the null values are replaced, not all the fields that have no null values correct?

abehrmann Regular Visitor
Regular Visitor

Re: Replace null values with contents from another column

I am attempting to replace the null values with the values from completion note date in the far left. 

 

 

 

 CompletionNotePIC.PNG

 

Any sugestions?

Aylarja Frequent Visitor
Frequent Visitor

Re: Replace null values with contents from another column

bdymit, thank you for sharing this. This solution is both straightforward and elegant, which I always prefer to approaches that have the look and feel of a hack or a workaround. The only mystery is why, even after all of this time, Microsoft still hasn't integrated this capability into the UI?

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors