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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jj101
Frequent Visitor

Need to remove numbers from a column

I have a column that has a mix of numbers and text. Example:

 

Column

ABC

ABC2

123

456

A1B1

 

I need to remove the ones that have only numbers (123, 456) not the ones that have text and numbers (ABC2, A1B1). 

 

I tried the custom column Text.Remove ([Column1], {"0".."9"} - however that also removes the numbers from the ones that have ABC2 and A1B1. 

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

Use this in a custom column

if Value.FromText([Column1]) is number then null else [Column1]

View solution in original post

4 REPLIES 4
ThxAlot
Super User
Super User

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVorVAdNGYIahkTGYNjE1g0gYOhkqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Transformed Column" = Table.TransformColumns(Source, {"Column", each if (try Number.From(_))[HasError] then _ else null})
in
    #"Transformed Column"

ThxAlot_0-1689960841971.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Vijay_A_Verma
Super User
Super User

Use this in a custom column

if Value.FromText([Column1]) is number then null else [Column1]

@Vijay_A_Verma thank you this worked lilke a charm!

Manoj_Nair
Solution Supplier
Solution Supplier

@jj101- Try this

if Text.Regex.IsMatch([Column], "^[0-9]+$") then null else [Column]

Let me know if this works.

 

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best Regards

 

Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors