Text.StartWith syntax help

[brycedwhite]

I'm trying to replace values in a single text column that begin with a specifc character.  Something like

Table.ReplaceValue(#"Merged Columns","3%",null,Replacer.ReplaceValue,{"Column2"}), but I can't figure out the correct syntax.

Thanks in advance for any help.

Bryce

[v-yanjiang-msft · Accepted solution]

Hi @brycedwhite ,

According to your description, I create a sample.

In my understanding, if the cell begin with 3% then replace the cell to null. Here's my solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWMlZ1AtPOYNLFWBUqqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type text}}),
    #"New" = Table.ReplaceValue(#"Changed Type",each [Column2], each if Text.StartsWith(Text.From([Column2]),"3%") then null else [Column2],Replacer.ReplaceValue,{"Column2"})
in
    #"New"

Get the correct result.

I attach my sample below for 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.

[ronrsnfld]

Might be easier to use the Table.TransformColumns function:

= Table.TransformColumns(#"Merged Columns",{"Column2", each if Text.StartsWith(_,"3%")
        then null else _})

OR

#"Starts with" = Table.TransformColumns(#"Merged Columns",{"Column2", each if Text.StartsWith(_,"3%")
        then Text.ReplaceRange(_,0,2,"") else _})