Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JoaoMS
Helper III
Helper III

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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"})

15.png

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

9 REPLIES 9

 

Please watch this video for conditional replace values : https://youtu.be/U5SzfXeuM5k

szabooimre
New Member

This is a simpler solution:

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

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

 

v-shex-msft
Community Support
Community Support

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"})

15.png

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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"})

 

This worked for me, not the original answer

 

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
Anonymous
Not applicable

Try this way : 

 

Step1 combine column " PO" and " Category" , 

Step2 replace"1006D" into "1006C" 

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

amitchandak
Super User
Super User

@JoaoMS , Something like this

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

 

This is new column script

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors