Forum Discussion

Vemul's avatar
Vemul
Helper I
2 years ago
Solved

Replace Values Based on Condition n Power Query

Hi Team,

please help, 

 

what I am looking is based on belwo table,

if Client is 99638 and Type is "Sample" then Client value to be changed to 99999.

if Client is 99567 and Type is "Sample" then Client value no chnage.

could oyu please provide me query to solve this

Thanks

VJ

 

Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi, Vemul 

     

    = Table.ReplaceValue(Your_Source, each [Type], 99999, (x,y,z)=> if x=99638 and y="Sample" then z else x, {"Client"})

    Stéphane

  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Vemul, 

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQ0M7ZQ0lHyL8pMz8xLzFGK1QEJmpqZAwWDE3MLclKhQhB1KEIQVQitsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Type = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Client", Int64.Type}}),
    ReplacedClient = Table.ReplaceValue(ChangedType,
        each [Client] = 99638 and [Type] = "Sample",
        each 99999,
        (x,y,z)=> if y then z else x,
        {"Client"} )
in
    ReplacedClient

6 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi, Vemul 

     

    = Table.ReplaceValue(Your_Source, each [Type], 99999, (x,y,z)=> if x=99638 and y="Sample" then z else x, {"Client"})

    Stéphane

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Vemul, 

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrQ0M7ZQ0lHyL8pMz8xLzFGK1QEJmpqZAwWDE3MLclKhQhB1KEIQVQitsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client = _t, Type = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Client", Int64.Type}}),
    ReplacedClient = Table.ReplaceValue(ChangedType,
        each [Client] = 99638 and [Type] = "Sample",
        each 99999,
        (x,y,z)=> if y then z else x,
        {"Client"} )
in
    ReplacedClient