Need Assistance with M Code - Changing Values in a Column based on a Condition in Another Column

[Ak14pbi]

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 believe I have everything correct. No errors occur when applying this M code in the advanced editor and closing out. However, in the "edit query" page, after this step is applied, the entire CLASS column - all rows now say "error" .. I can't figure out what I'm doing wrong. Should I be using the ReplaceValues function? 

My code (previous step is called Replaced Value1) :

#"Updated Column" = Table.TransformColumns(#"Replaced Value1", {{"CLASS", each

     if Text.Contains([DOC_TEXT], "sale", Comparer.OrdinalIgnoreCase) then "Direct Sale"

     else [CLASS], type text}})

[Anonymous]

Hi @Ak14pbi,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are trying to transfrom a column when there's a certain value (sale) in it. As @Omid_Motamedise, @dufoq3 and @ZhangKun provided solutions from their side. Please go through the solutions provided and mark the helpful reply as solution.

I would also take a moment to thank @Omid_Motamedise, @dufoq3 and @ZhangKun, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[Omid_Motamedise]

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.

[Ak14pbi]

Thank you so much!

[dufoq3 · Accepted solution]

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

[Ak14pbi]

Thank you so much for your help!

[dufoq3]

You're welcome.

[ZhangKun]

Yes, you should use ReplaceValues.

One very important difference is that TransformColumns cannot access other columns, but ReplaceValue can do it.

[Ak14pbi]

Thank you for your help!