Forum Discussion
Solved
Need Assistance with M Code - Changing Values in a Column based on a Condition in Another Column
There are two columns, CLASS and DOC_TEXT. Where rows under DOC_TEXT contain (not equal to) the word "sale", values under column CLASS must change to "Direct Sale". I used TransformColumns and I beli...
Hi Ak14pbi, check this:
Output
If you want to seach for "sale" case insensitive, replace Comparer.Ordinal with Comparer.OrdinalIgnoreCase
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUSrOz01VKEmtKFGK1YlWcgKKJCYlKxQn5qQqpKalgwWdQcqAAmCOC5CTX5KRWgTVFAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CLASS = _t, DOC_TEXT = _t]), ReplacedClass = Table.ReplaceValue(Source, each Text.Contains([DOC_TEXT], "sale", Comparer.Ordinal), each "Direct Sale", (x,y,z)=> if y then z else x, {"CLASS"} ) in ReplacedClass
In transforming the column, you are not allwed to refer to other column like [CLASS], so as mentioned use replace command or add a new column, or merge the both column.
Thank you so much!