Table Replace Value multiple condition

[Anonymous]

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

[v-piga-msft]

Hi @Anonymous ,

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

Here is the output.

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

[gpoggi · Accepted solution]

Hi @Anonymous ,

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

[ARCpatcalderon]

This worked for me. Thank you!

[gvg]

Hi,

How do I change this to be used in a function with column name as a variable ?

[Zubair_Muhammad]

@Anonymous 

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])

[Greg_Deckler]

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