Forum Discussion

Ak14pbi's avatar
Ak14pbi
New Member
1 year ago
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...
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.