Forum Discussion
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
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!
19 Replies
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!
6 years later still very helpful!
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?
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.
I am attempting to replace the null values with the values from completion note date in the far left.
Any sugestions?
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]
Hi Matt!
I wrote something like : if [Session Date] = null then "Not Passed" else if [Expiration Date] < Date.From(DateTime.LocalNow()) then "Expired" else if [Expiration Date] = null then [Status] else "Ok"
but in result column error values appeared in cells where should be Status columns values. Other cells according to formula.
Could you please help me to solve it?
I just wrote this in a test file, and it worked fine
each if [Session Date] = null then "Not Passed" else if [Exp Date] < Date.From(DateTime.LocalNow()) then "Expired" else if [Exp Date] = null then [Status] else "OK"
check that all your date columns are correctly formatted as Date before this step and that the Status column is correctly formatted as text
Best!
Just a tweak that worked for me
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
There is a need for a space between the each and the [Values Column] but I didn't find one was needed before. Also the _ threw an error
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
A B C X 10 10 X.1 null 10 X.2 null 10 Y 11 11 Y.1 null 11 Y.2 null 11 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.
Hey everyone!
Thank you for the video which was very helpful - I was just curious if anyone knew a way to replace a null value with a new value specific on a different column but not matching the other column? e.g. If 'PRODUCT NAME' includes 'Barbie' or 'Playdough' replace with 'Toys', If 'PRODUCT NAME' includes 'Tea', 'Biscuits', 'Noodles' replace with 'Consumables', etc.
Essentially I have several hundred thousand rows each with a unique sales value. Most have a category allocated already, but some have been left blank. I want to replace the nul value with one of 5 categories depending on the product brand in the name.
No clue if this is even a remote possibility but thought it was worth asking as I am stumped.Something like this should work I think
// Replace null values in a ExistingColumnWithNulls based on new conditions
ReplaceNulls = Table.ReplaceValue(
#"PreviousStep",
null,
each if Text.Contains(_[PRODUCT], "Barbie") or Text.Contains(_[PRODUCT], "Playdough") then "Toys"
else if Text.Contains(_[PRODUCT], "Tea") or Text.Contains(_[PRODUCT], "Biscuits") or Text.Contains(_[PRODUCT], "Noodles") then "Consumables"
else null,
Replacer.ReplaceValue,
{"ExistingColumnWithNulls"}
)
in
ReplaceNulls
