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

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

    Hello,

     

    Thanks, it's working. It is not very convenient because I must make 3 different M requests for each modality of Attribut column but it suits me.

     

    Thanks for your reactivity !

     

    -- 1
= Table.ReplaceValue(#"Lignes filtrées",each [Attribut], null, (x, y, z)=> if y = "Freezer stock" then 0 else x,{"cupboard 1 stock value", "cupboard 2 stock value"})

-- 2
= Table.ReplaceValue(#"Personnalisé1",each [Attribut], null, (x, y, z)=> if y = "cupboard 1 stock" then 0 else x,{"Freezer stock value", "cupboard 2 stock value"})
-- 3
= Table.ReplaceValue(#"Personnalisé2",each [Attribut], null, (x, y, z)=> if y = "cupboard 2 stock" then 0 else x,{"Freezer stock value", "cupboard 1 stock value"})