Forum Discussion

umaluagr's avatar
umaluagr
Regular Visitor
2 years ago
Solved

Replace letters with null

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

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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"

     

1 Reply

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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"