Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Chrisjr
Helper IV
Helper IV

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1669978738403.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
Chrisjr
Helper IV
Helper IV

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 

Chrisjr_0-1669979118264.png

 

Do you perhaps know the reason ? 

 

@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.

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




thanks a lot @BA_Pete 

BA_Pete
Super User
Super User

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:

BA_Pete_0-1669978738403.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors