Set Value of Column B based on Value in Column A

[Mquallet]

Two columns, A and B in same table.
A has names of classes in it.  B is their category
If colA contains certain words = '*open chat*' id like to set colB to 'Chat", else leave it alone.

Simplest manner to accomplish this?

[v-kalyj-msft · Accepted solution]

Hi @Mquallet ,

According to your description, here's my solution.

#"Transform"=Table.ReplaceValue(#"Changed Type",each[ColumnB],each if Text.Contains([ColumnA],"*open chat*") then "Chat" else[ColumnB],Replacer.ReplaceValue,{"ColumnB"})

I create a sample and get the correct result:

Here's the whole M syntax, you can copy-paste it in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0sovSM1TSM5ILNFS0lFyU4rViVZyBrLcwSwXIMsDzHJFVeilFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", type text}, {"ColumnB", type text}}),
    #"Transform"=Table.ReplaceValue(#"Changed Type",each[ColumnB],each if Text.Contains([ColumnA],"*open chat*") then "Chat" else[ColumnB],Replacer.ReplaceValue,{"ColumnB"})
in
    #"Transform"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

NewStep=Table.ReplaceValue(PreviousStepName,"open chat",each [colA],(x,y,z)=>if Text.Contains(z,y) then "Chat" else x,{"colB"})