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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors