Replace letters with null

[umaluagr]

Hi community

Is there a possibility to replace letters in a column with "null"?

I have a column that contains rows with letters and rows with numbers. I want to replace only rows with letters with a null value.

e.g.

ABC

123

DEF

234

should result in

null

123

null

234

[dufoq3 · Accepted solution]

Hi @umaluagr, add this as a new step.

Replace

• Source with your previous step reference
• [Column1] with your [ColumnName]
• "Column1" with your "ColumnName"

= Table.ReplaceValue(Source,
        each try Number.From([Column1]) otherwise false is number,
        each null,
        (x,y,z)=> if y then x else z,
        {"Column1"} )

Whole code with sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVorViVYyNDIG045OzmDaxNRMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,
        each try Number.From([Column1]) otherwise false is number,
        each null,
        (x,y,z)=> if y then x else z,
        {"Column1"} )
in
    #"Replaced Value"