Forum Discussion
Helper IReplace 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]
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.
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!
Resolver IISaw 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.
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!
QCKudo Kingpin
6 years later still very helpful!
omrdmrWhile 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
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.
FYI, it seems the data type change bug is no there, I'm not having any issues with that currently.
abehrmannHelper II
I am attempting to replace the null values with the values from completion note date in the far left.
Any sugestions?
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?
- trt18-sistemas
Advocate IV
thank you. That was what I was looking for. Perfect.
This was right on for me. I have a list of codes and need to only translate some of them to an alternate code. ex. a,b,c,y. merge translate table y=d. all nulls in expanded merge collumn = replace values for each null then original value a,b,c.
Perfect sir!!!
