Forum Discussion

Imad-R's avatar
Imad-R
Frequent Visitor
3 years ago
Solved

Replace value for several column depend condition power query

Hello,   The following will be an example of my problem.  Here are the data :    I want to filter these data by type of stock so I unpivot the column related to the stock number, and we ob...
Need Help
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi Imad-R,

     

    See if the code below works as you want. You can then modified to suite your case:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MTlXSUTIEYrei1NQqpVgdFEHXnOJUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Attribute = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"Attribute", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Attribute], null, (x, y, z)=> if y= "Freez" then 0 else x,{"Value"})
in
    #"Replaced Value"

     

    Cheers,

    John

jbwtp's avatar
jbwtp
Icon for Memorable Member rankMemorable Member
3 years ago

Hi Imad-R,

 

See if the code below works as you want. You can then modified to suite your case:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck7MTlXSUTIEYrei1NQqpVgdFEHXnOJUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Attribute = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"Attribute", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Attribute], null, (x, y, z)=> if y= "Freez" then 0 else x,{"Value"})
in
    #"Replaced Value"

 

Cheers,

John