Power Query change column text based on a condition

[amay]

Hello,

I need to replace values in power query based on a condition:

If Column B contains 31456 or 42987 or 45028 change Column A to "Direct sales" otherwise leave column A as is.  I've tried every example of code I can find on here and none work.

[jbwtp · Accepted solution]

Hi @amay,

if you want to actually replace  rather then add column and then delete/rename (which I think just as equally Ok performance-wise), you can try this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcfLFYAgDATAXvbMARJQOPq3h7z034a6Zm5jhgUJWmqb4Mmwvqsy+sxt31qWzu2xwR3/NHNnrHBXTLg7pnB/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [B], "Direct Sales", (x, y, z)=> if List.Contains({31456, 42987, 45028}, y) then z else x,{"A"})
in
    #"Replaced Value"

Cheers,

John

[sudhav]

you can try this. add a conditional column

refer two pics.

If I answered your question, please accept it as solution and give kudos.