REPLACE CHARACTERS TO WHITE

[Syndicate_Admin]

Hello everyone good afternoon.

One question, how do I do to do the next operation, In a Phone Column there is some data that is only 4 characters or less, the idea is that data containing 4 or fewer characters will be replaced to Blank.

ColumnaTelefonos

12345678

1234567

1234

1

345

25

1188

123456

make it look like this.

ColumnaTelefonos

12345678

1234567

BLANK

BLANK

BLANK

BLANK

BLANCK

123456

Thank you for any help

[v-xiaotang · Accepted solution]

Hi @Syndicate_Admin 

It would be easy if you use Custom Column,

if Text.Length([ColumnaTelefonos]) <=4 then null else [ColumnaTelefonos]

If you don't need the original column, you can remove it

OR

If you want to solve it by advanced editor, you can take steps below for reference:

 let
    Source = Excel.Workbook(File.Contents("C:\Temp Testing Only\SampleData.xlsx"), null, true),
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ColumnaTelefonos", type text}}),
    #"Custom1" =Table.TransformColumns(#"Changed Type",{"ColumnaTelefonos", each if Text.Length(_) <= 4 then null else _,type text})
in
    Custom1

 then, you will get the following:

Hope it helps.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Super funcion me a thousand thanks 😄

[Syndicate_Admin]

Hello, thank you for your response look I did what you tell me but I get a mistake.

I don't think I'm doing wrong.

[mahoneypat]

You can do a custom transform on the column.  If that column is a number column, you can add a step after (hit the fx button to the left of the formula bar) and replace it (in the formula bar) with this.  Replace #"Filtered Rows" with the name of your previous step.

= Table.TransformColumns(#"Filtered Rows", {{"ColumnaTelefonos", each if _ <= 9999 then null else _, type number}})

If it is a text column, you can use this instead.

= Table.TransformColumns(#"Filtered Rows", {{"ColumnaTelefonos", each if Text.Length(_) <= 4 then null else _, type text}})

Pat