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...
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

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