Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

ReplaceValue with multiple condition

Hi Im seeking for help on ReplaceValue query

 

I have a qeury table

Item IDSize
2810-00131150
3050-12568150

 

I want to set 

If [Item ID] = "2810-00131", then

     if "Size" = "150", then change to "105"

 

 

I wrote the query, but not working.

 

#"Replaced Value" = Table.ReplaceValue(Source,[Size],each if Record.Field({[Item ID]="2810-00131" and [Size]="150" then "105" },Replacer.ReplaceValue,{"Size"}))

 

 

May I know why?

 

Thanks!

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    To add a custom column as below.

    if [Item ID] = "2810-00131" then if [Size] = 150 then 105 else then [Size]

     

     

    M code for your reference.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIwNNA1MDA0NlTSUTI0NVCK1YlWMjYwNdA1NDI1s4AJxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t, Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Item ID] = "2810-00131" and [Size] = 150 then 105 else [Size])
in
    #"Added Custom"
  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks v-frfei-msft 

     

    I summarized your suggestion and create the working "Table.ReplaceValue" :

     

    #"Replaced Value" = Table.ReplaceValue(Source,each [Size],each if [Item ID]="2810-00131" and [Size]="150" then "105" else [Size],Replacer.ReplaceText,{"Size"})

     

     

    Regards,

    Henry

8 Replies

  • adityavighne's avatar
    adityavighne
    Continued Contributor
    6 years ago

    Anonymous 

     

    Create a new column and write below DAX

     

    Replace = IF(ID=2810-00131, IF(Size=150, 105), Size)
     
    Regards,
    Aditya Vighne
     
    If the problem is resolved then accept this as a solution.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks adityavighne , however I want to do in Query Editor, since it is not a one-off adjustment, more data will be update with same problem. Plus there will be more adjustments, like 

       

      If Item ID = 1010-28000 then replace "20" to "30"

      If Item ID = 1030-28000 then replace "120" to "50"

      etc

       

      Regards,

      Henry

      • adityavighne's avatar
        adityavighne
        Continued Contributor
        6 years ago

        Anonymous 

        go for a conditional column in Query editor. below is the e.g need to replace as per your requirements

         

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    To add a custom column as below.

    if [Item ID] = "2810-00131" then if [Size] = 150 then 105 else then [Size]

     

     

    M code for your reference.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrIwNNA1MDA0NlTSUTI0NVCK1YlWMjYwNdA1NDI1s4AJxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t, Size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Size", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Item ID] = "2810-00131" and [Size] = 150 then 105 else [Size])
in
    #"Added Custom"
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks v-frfei-msft 

       

      I summarized your suggestion and create the working "Table.ReplaceValue" :

       

      #"Replaced Value" = Table.ReplaceValue(Source,each [Size],each if [Item ID]="2810-00131" and [Size]="150" then "105" else [Size],Replacer.ReplaceText,{"Size"})

       

       

      Regards,

      Henry