Replace null values of one column to another column.

[Nasif_Azam]

Dear Contributors, 
I hope you guys are well. I need a help in power query to Replace null values of one column to another column.

I have 2 column namely OldKey and NewKey. 

OldKey has not any nulls but on the other hand NewKey has some nulls. So I would like to replace the NewKey null values by using OldKey values. 

Sample Data:

OldKey	NewKey
A	A
B	B
C	null
D	null
E	null

Expected Output:

OldKey	NewKey	FinalKey
A	A	A
B	B	B
C	null	C
D	null	D
E	null	E

The idea is,

if NewKey = null then replace the null using OldKey

if NewKey != null then keep the NewKey values as it is.

Please suggest some power query or m code or custom column or replace value.

Thanks in advanced.

Best Regards,

Nasif Azam

[ronrsnfld · Accepted solution]

@Nasif_Azam You write you want to "Replace" but your example shows you creating an additional column.

If you truly want to "Replace" the NewKey null then try the code below using Table.ReplaceValue function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYnWglJyDLCcxyBrLySnNywBwXZI4rnBMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OldKey = _t, NewKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OldKey", type text}, {"NewKey", type text}}),
    #"Replace Null" = Table.ReplaceValue(
        #"Changed Type",
        each [NewKey],
        each [OldKey],

//if null is a text string, then use as below
//if null is a true null, then remove the quote marks below
        (x,y,z) as text=>if y= "null" then z else x,
        {"NewKey"}
    )
in
    #"Replace Null"

Note that your example shows null as a text string. The code takes that into account. However, if the null is really a true null, then remove the quote marks around null in the Replace.Value function.

Before:

After

[raisurrahman]

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OldKey", type text}, {"NewKey", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "FinalKey", each if [NewKey] = null then [NewKey] else [OldKey])
in
#"Added Conditional Column"

[Royel · Accepted solution]

Hi @Nasif_Azam 

check this out 

let
    Source = Table.FromRows({
        {"A", "A"},
        {"B", "B"},
        {"C", null},
        {"D", null},
        {"E", null}
    }, {"OldKey", "NewKey"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OldKey", type text}, {"NewKey", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FinalKey", each [NewKey] ?? [OldKey])
in
    #"Added Custom"

Results: 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[ronrsnfld · Accepted solution]

@Nasif_Azam You write you want to "Replace" but your example shows you creating an additional column.

If you truly want to "Replace" the NewKey null then try the code below using Table.ReplaceValue function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYnWglJyDLCcxyBrLySnNywBwXZI4rnBMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OldKey = _t, NewKey = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OldKey", type text}, {"NewKey", type text}}),
    #"Replace Null" = Table.ReplaceValue(
        #"Changed Type",
        each [NewKey],
        each [OldKey],

//if null is a text string, then use as below
//if null is a true null, then remove the quote marks below
        (x,y,z) as text=>if y= "null" then z else x,
        {"NewKey"}
    )
in
    #"Replace Null"

Note that your example shows null as a text string. The code takes that into account. However, if the null is really a true null, then remove the quote marks around null in the Replace.Value function.

Before:

After

[SundarRaj]

Hi @Nasif_Azam,
What you can do is use Table.TransformRows instead of creating a custom column. Sure, that works but it adds an additional step. Using Table.TransformRows gives you the desired solution. I'll attach the image of the code and file as well for your reference.

Thanks,

[GeraldGEmerick]

@Nasif_Azam You can create a custom column like the following:

= if [NewKey] = null then [OldKey] else [NewKey]

Or you could just use the OldKey column?