Replace numbers with blank in a column containing text and numbers.

[Anonymous]

I have a column containing text and numbers. I want to replace the numbers with blank. Any idea how to do that.

1

2

3

a

b

c

d

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can add a custom column in query editor with the following formula to achieve your requirement:

#"Added Custom" = Table.AddColumn(Source, "Custom", each if Value.Is(Value.FromText([Desc]),type number) then null else [Desc])

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY9RC4IwEMe/ivgcoUbQq2WKkSjNN5FjusMGuoWbhd8+GSWB9vKD3e/udv+isEMCV/nCHlk0cEZFjdBjjaIeLbvcGH+jXP3TOYGnbMH1nG93Rkc56BvVXM4bzsc4D3xIaN9wAVpChtgra2VkKYOMwEkKlSBbShL92eps3cPvgd5+PjCXmra+UqjVp2RdsnT6o3u0qJfx40mGvF0Gryq4V1Sw9Zhzm8BmSvZEgkLzbsJszokPJDUv19Az3BlSw8qwNmR2Wb4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Desc = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Value.Is(Value.FromText([Desc]),type number) then null else [Desc])
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[amitchandak]

Dax

=IF(ISNUMBER([fiedl]), blank(), [field])

[Anonymous]

A very straight forward solution is to add a new column during import and use code like:

if Value.Is([OldColumn], type text) then [OldColumn] else null

You could remove or keep the old column as needed.  You can also rename the new column if you want to keep your model happy.

[Anonymous]

I have alphanumeric characters in some of the rows . Your suggested code is not working. I changed the data type to any but doesn't remove the numeric row. 

[Anonymous]

@Anonymousyou are right, this isn't converting correctly.  Please see this previous solution

https://stackoverflow.com/questions/36768353/power-query-replace-all-numbers-with-null