Forum Discussion
Solved
Power Query to replace specific value in column based on value in another column
Hi, I hope you are well. From the below I am trying to replace the 'none of the above' values in characteristic 1 column to a blank or null if the characteristic 2 column does not equal 'none...
Hi mahoneypat
Thanks for your reply
I managed to figure it out in the Power Query Editor, thank you though!
= Table.ReplaceValue(#"Changed format to text","None Of The Above", each if [Characteristic 2] <> null then null else "None Of The Above",Replacer.ReplaceValue,{"Characteristic 1"})
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. Basically, you just need to do a basic Replace Values step with "none of the above" and "null" in the pop-up box (no quotes), and then replace the second term ("null") with
each if [Characteristic 2] = "none of the above" then null else [Characteristic 1]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcrLz0tVyE9TKMlIVUhMyi9LBYoVpaYoxepEKzkB2ZWpOTn55UBGUk5pKljUGYcuTLHYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, #"Characteristic 1" = _t, #"Characteristic 2" = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,"none of the above",each if [Characteristic 2] = "none of the above" then null else [Characteristic 1],Replacer.ReplaceValue,{"Characteristic 1"})
in
#"Replaced Value"
Pat
Hi mahoneypat
Thanks for your reply
I managed to figure it out in the Power Query Editor, thank you though!
= Table.ReplaceValue(#"Changed format to text","None Of The Above", each if [Characteristic 2] <> null then null else "None Of The Above",Replacer.ReplaceValue,{"Characteristic 1"})