Forum Discussion

JoaoMS's avatar
JoaoMS
Helper III
5 years ago
Solved

Replace value in Power Query based on condition

Dear all,

 

I have a table ("POs") with a list of POs which have some fields that in very specifics moments have wrong entry inputs. For example in the following table the PO "1006" should have the Category "C" instead of the Category "D":

 

PO Category Amount
1001 A 1050
1002 A 1327
1003 B 1276
1004 B 1383
1005 C 1967
1006 D 1288
1007 E 1707
1008 F 1233

 

I tried to use the ReplaceValue option in Power Query but it replaces all the the Categories "D" of all the "POs". I just want to replace the category of the PO "1006".

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI JoaoMS,

    You can use replace value function with custom expression to add conditions to replace value steps:

        #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Category],each if [PO]=1006 and [Category]="D" then "C" else [Category],Replacer.ReplaceText,{"Category"})

    Regards,
    Xiaoxin Sheng

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI JoaoMS,

    You can use replace value function with custom expression to add conditions to replace value steps:

        #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Category],each if [PO]=1006 and [Category]="D" then "C" else [Category],Replacer.ReplaceText,{"Category"})

    Regards,
    Xiaoxin Sheng

    • Martin_Nagy's avatar
      Martin_Nagy
      Advocate I
      3 years ago

      I think you have an error in the syntax, should be Replacer.ReplaceValue instead of Replacer.ReplaceText. The whole line is thus:

      #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Category],each if [PO]=1006 and [Category]="D" then "C" else [Category],Replacer.ReplaceValue,{"Category"})

       

      • gabriel007's avatar
        gabriel007
        Regular Visitor
        2 years ago

        This worked for me, not the original answer

         

    • JoaoMS's avatar
      JoaoMS
      Helper III
      5 years ago
      Thank you, this is what I was looking for without creating a new column. I just made a small change "each if [PO]=1006 then "C" else [Category]", considering that there is just one PO named "1006" Regards
  • szabooimre's avatar
    szabooimre
    New Member
    4 years ago

    This is a simpler solution:

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","D",each if [PO]=1006 then "C" else "D",Replacer.ReplaceText,{"Category"})
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    JoaoMS , Something like this

    if [PO] = "1006" then Text.Replace([Category], "C", "D") else [Category]

     

    This is new column script

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Try this way : 

     

    Step1 combine column " PO" and " Category" , 

    Step2 replace"1006D" into "1006C" 

    Step3 Split the combined column to " PO" and " Category"

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Dear community, I've got also issue with the creation of the "replace" code.

    I need to replace value based on multiple condition.

    I've attached and extract of the data that I want to manage.

    They are the conditions that I would like to apply :

    If [Billing code - description] = "--Unknown billing code--" AND [User - name] = "svMedIS_App Compte de service RadOffice" AND [Location] = "Rennaz" then [Billing code] = "A" else "No change"

    If [Billing code - description] = --Unknown billing code-- AND [User - name] = svSIMop Compte de service Logimen AND [Location] = Rennaz then [Billing code] = "B" else "No change"

    If [Billing code - description] = --Unknown billing code-- AND [User - name] = svSIMop Compte de service Logimen AND [Location] = Providence then [Billing code] = "C" else "No change"

     

    here the dataset I used for my test 

    Thanks for your reply and help