Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to Solution.
Use this in a custom column
if Value.FromText([Column1]) is number then null else [Column1]
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Use this in a custom column
if Value.FromText([Column1]) is number then null else [Column1]
@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/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
18 | |
15 | |
10 | |
8 | |
8 |