Forum Discussion

Chrisjr's avatar
Chrisjr
Helper IV
3 years ago
Solved

replace numbers by a specific text in a column

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

Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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

  • Chrisjr's avatar
    Chrisjr
    Helper IV
    3 years ago

    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's avatar
      Chrisjr
      Helper IV
      3 years ago

      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's avatar
        BA_Pete
        Super User
        3 years ago

         

        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