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

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

     

8 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    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

     

  • ZhangKun's avatar
    ZhangKun
    Super User
    1 year ago

    Yes, you should use ReplaceValues.

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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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.

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