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

     

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"