Forum Discussion

negbc's avatar
negbc
Icon for Helper II rankHelper II
2 years ago
Solved

Replacing Multiple Cell Values

I have a column with various prices and blank cells, I want to replace all the different $ amounts to a 'rebate' text and then all the blank cells to 'no rebate' text. I could indiviudal search the $...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIztrRUitWJVlIAk4amYMrUQM8QSRRCGuiBlMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Original],each if [Original]=null then "No Rebate" else "Rebate",Replacer.ReplaceValue,{"Original"})
in
    #"Replaced Value"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIztrRUitWJVlIAk4amYMrUQM8QSRRCGuiBlMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Original],each if [Original]=null then "No Rebate" else "Rebate",Replacer.ReplaceValue,{"Original"})
in
    #"Replaced Value"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

negbc's avatar
negbc
Icon for Helper II rankHelper II
2 years ago

Great, thanks!