Forum Discussion
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 of the above' i.e. it has a characteristic in there such as age, etc.
I need to avoid adding a custom column so I need a Power Query statement that will allow me to do this rather than DAX.
Any assistance you can provide in this matter would be much appreciated!
Thank you in advance!
@amitchandak parry2k Fowmy selimovd amitchandak Jihwan_Kim
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"})
2 Replies
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"})
