Replace col1 value with value from col2 only if col2 is not null - Power Query

[adriansuteu]

Hello.

I have two columns in Power BI (Lets called them for this example col1 and col2)

col2 will only have data in some rows, while col1 will have data in every row.

I need to replace the values in col1 with the values in col2 (just for the rows where col2 has data - it is not null).

I know I can create a third column and populate it with the data from col1 or col2 very easy, but I have a lot of relationships, active and inactive, as well as many measures that use col1. I would prefer to replace the values within this column in Power Query.

Thank you,

Adrian

[v-joesh-msft · Accepted solution]

Hi @adriansuteu ,

Try M Query similar to the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYlOlWJ1oJScgywiIwRxnIMMYiC3APBcgywQm5QrWoaNkCea5AVlmIHMMwVx3INMcrDIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}}),
    Custom1  = Table.ReplaceValue(#"Changed Type",each [col1],each if[col2] <> null then [col2] else [col1],Replacer.ReplaceValue,{"col1"})

in
  Custom1

 "Custom1  = Table.ReplaceValue(#"Changed Type",each [col1],each if[col2] <> null then [col2] else [col1],Replacer.ReplaceValue,{"col1"})"

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfncbKstoQhNp1wiFo... 

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.