cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rafaelsmoreno Frequent Visitor
Frequent Visitor

Conditional Table.ReplaceValue (if to define it replace will be applied or not)

Hi

 

I have query with 3 columns: InvoiceNumber, InvoiceType and InvoiceAmount. There are 40K+ records, and half a dozen of them have the wrong InvoiceType (need to change com "Service" to "Product"

 

So, I put up this code:

 

replaceType =
if "InvNumber" = "30881" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "30769" and "InvType" = "Service" then Table.ReplaceValue(PreviousStep, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "30770" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "30771" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "30772" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "30791" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "31261" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "31262" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "31336" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else if "InvNumber" = "31337" and "InvType" = "Service" then Table.ReplaceValue(replaceCarga, "Service", "Product", Replacer.ReplaceText, {"InvType"})
else "InvType"="InvType"
in
replaceType

 

The thing is it is not working at all. I don't know how to instruct a "do nothing" in the last else. Any ideas?

4 REPLIES 4
Super User
Super User

Re: Conditional Table.ReplaceValue (if to define it replace will be applied or not)

@rafaelsmoreno

 

I believe you can also create a quick table of replacements and then merge the 2 fields of both tables

InvNumber InvType Correct Replacement
30881 Service Product
30769 Service Product
30770 Service Product

 

 

 

 

rafaelsmoreno Frequent Visitor
Frequent Visitor

Re: Conditional Table.ReplaceValue (if to define it replace will be applied or not)

Zubair, that seems to be the easier way to go, thanks for that. I'll use it, because in the end of the day, it solves the problem. 

 

In the meantime I really wanted to improve my "IF" skills. This "do nothing' at the end of the if then else statement is really troublesome.  Let's see if somebody come up with an insight on that. 

 

Best regards, 

Community Support Team
Community Support Team

Re: Conditional Table.ReplaceValue (if to define it replace will be applied or not)

@rafaelsmoreno

 

Is this query a custom function or others? Which error occurs when you implement it?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rafaelsmoreno Frequent Visitor
Frequent Visitor

Re: Conditional Table.ReplaceValue (if to define it replace will be applied or not)

Hi

 

It's the standard replace value function from power query. The point is that I cannot make it work with if statements inside it. 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 405 members 3,659 guests
Please welcome our newest community members: