Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @mahoneypat
Thanks for your reply
I managed to figure it out in the Power Query Editor, thank you though!
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat
Thanks for your reply
I managed to figure it out in the Power Query Editor, thank you though!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.