How to replace specific values with null in a column

[Imposed]

Hello!

I would like to replace the values that have 5 numbers in them with "null" and keep the ones with 2-3 numbers. How would i proceed with that?

[slorin]

Hi

..., each if Text.Length([Custom]) > 4 then null else [Custom], ...

Stéphane 

[ImkeF · Accepted solution]

Hi @Imposed ,
you can do a dummy-replace-operation on that column and then tweak the resulting M-code a bit:
Table.ReplaceValue(#"Changed Type", each [Column1], each if Text.Length([Column1]) > 3 then null else [Column1], Replacer.ReplaceValue, {"Column1"} )

I have described this method here: Table.TransformColumns - alternative in PowerBI and PowerQuery in Excel (thebiccountant.com)

Or you paste hte following code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WMjQAAqVYnWglIzjLEEobmxqCaVMzc6XYWAA=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t]
    ), 
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Column1", type text}}), 
    #"Replaced Value" = Table.ReplaceValue(
        #"Changed Type", 
        each [Column1], 
        each if Text.Length([Column1]) > 3 then null else [Column1], 
        Replacer.ReplaceValue, 
        {"Column1"}
    )
in
    #"Replaced Value"

Table.ReplaceValue(#"Changed Type", each [Column1], each if Text.Length([Column1]) > 3 then null else [Column1], Replacer.ReplaceValue, {"Column1"} )
 

[yasiralifeeds]

Hi,

I am facing the issue in replacing values greater than text lenght 4 and below error is displaying in custom column function.

Your help will be highly appreciated.