Forum Discussion
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.
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
6 Replies
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
Hi,
How do I change this to be used in a function with column name as a variable ?
This worked for me. Thank you!
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
Calling in the master of M, the principal of Power Query, the...well...you get the idea. Help ImkeF !!
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])
