replace numbers by a specific text in a column

[Chrisjr]

Hello all, 

I have the following column:

Colum title

0.5

index1

index2

index3

0.9

index5

index6

I would like to replace the numbers only by a specific text. Howerver, the numbers next to the index word should not be impacted. 

These numbers can change from tables to tables and therfore are not always the same.

The result I am looking for would be :

Colum title

IndexType

index1

index2

index3

IndexType

index5

index6

Thank you for the help

[Chrisjr]

Hi @BA_Pete ,

Thanks for your quick reply. 

so I did the following in the power query editor = Table.AddColumn(#"Added Index", "Custom", each if try Value.Is(Number.From([#"Column1"]), type number) otherwise false then "IndexType" else [#"Column1 "])

It does replace the numbers but  I get an error in the text cells 

Do you perhaps know the reason ? 

[Chrisjr]

@BA_Pete I noticed I put an additional space in my formula. It worked. Thanks a lot for the help. 

would there be a way to transform the column directly instead of adding a colmun? 

The reason I am asking is because I am worried it could slow down the refresh when new data are added.

[BA_Pete]

There is a way to replace the values in-column but, if your only concern is refresh speed, then leave it as a new column, it should be faster.

Pete

[Chrisjr]

thanks a lot @BA_Pete 

[BA_Pete · Accepted solution]

Hi @Chrisjr ,

Put this into a new custom column:

if try Value.Is(Number.From([#"Colum title "]), type number) otherwise false then "IndexType" else [#"Colum title "]

Example output:

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzVYrViVbKzEtJrTBEMI0QTGMw00DPEiGEpMdMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colum title " = _t]),
    addCustom = Table.AddColumn(Source, "Custom", each if try Value.Is(Number.From([#"Colum title "]), type number) otherwise false then "IndexType" else [#"Colum title "])
in
    addCustom

Pete