cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yekang02 Visitor
Visitor

Table Replace Value multiple condition

= Table.ReplaceValue(#"Expanded Code",each ([Geographical]="" or Text.Contains([Geographical],"INV")=true),"TBA",Replacer.ReplaceValue,{"Geographical"})

 

want to replace value to "TBA" if coulumn "Geographical" has null value or "INV".

Fomular has no error but result is not correct.

help~~ please.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
gpoggi Regular Visitor
Regular Visitor

Re: Table Replace Value multiple condition

Hi @yekang02 ,

 

Let me know if I get you well but this option helps you with what you need?

 

https://drive.google.com/open?id=1LlxMa2ScsJpsf0ZcCJ31vo5a0kyvbIZT

 

Also there are other options like custom column and create a new column based on other column values  but if you need to use the replace function in the same column that is how I would do it.

 

Any question, just let me know.

 

Regards,

 

Gian Carlo Poggi

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Table Replace Value multiple condition

Calling in the master of M, the principal of Power Query, the...well...you get the idea. Help @ImkeF !!


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Table Replace Value multiple condition

@yekang02 

 

With Table.ReplaceValue we can only replace one value at a time but you can specify multiple columns to make replacement in

 

https://docs.microsoft.com/en-us/powerquery-m/table-replacevalue

 

Alternatively, we can use a simple if statement

 

 =Table.AddColumn(#"Expanded Code", "Custom", each if [Geographical]="" or Text.Contains([Geographical],"INV") then "TBA" else [Geographical])
Try my new Power BI game Cross the River
Highlighted
gpoggi Regular Visitor
Regular Visitor

Re: Table Replace Value multiple condition

Hi @yekang02 ,

 

Let me know if I get you well but this option helps you with what you need?

 

https://drive.google.com/open?id=1LlxMa2ScsJpsf0ZcCJ31vo5a0kyvbIZT

 

Also there are other options like custom column and create a new column based on other column values  but if you need to use the replace function in the same column that is how I would do it.

 

Any question, just let me know.

 

Regards,

 

Gian Carlo Poggi

View solution in original post

Community Support Team
Community Support Team

Re: Table Replace Value multiple condition

Hi @yekang02 ,

By my tests, the suggestion of Zubair_Muhammad should be right.

Here is the output.

Capture.PNG

If you have solved the problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, I think you could use the easiest way that you could replace the null to "TBA" then replace the "INV" to "TBV".

With that way, you don't need to code the power query, you could only use the option "Replace value" by right clicking the column.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,282)