Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello fellow DAX'ers,
This seems like it should be easy. I have a column of text data coming into a zip code field. It's public data meaning sometimes the data reads N/A or the name of a town etc
ISTEXT() doesn't discriminate whether there are only numbers--it's a text column, so doesn't seem to help.
Is there a "wildcard" for TRIM or SUBStITUTE to mean any Alpha characters? Or the opposite...Numeric characters. Seems pretty useful.
Any ideas?
Tom
Solved! Go to Solution.
Messy, but you could do this:
NoText = Var NoAs = SUBSTITUTE([address],"a","") Var NoBs = SUBSTITUTE(NoAs,"b","") Var NoCs = SUBSTITUTE(NoBs,"c","") Var NoDs = SUBSTITUTE(NoCs,"d","") Var NoEs = SUBSTITUTE(NoDs,"e","") Var NoFs = SUBSTITUTE(NoEs,"f","") RETURN NoFs
Rinse and repeat for all characters.
Messy, but you could do this:
NoText = Var NoAs = SUBSTITUTE([address],"a","") Var NoBs = SUBSTITUTE(NoAs,"b","") Var NoCs = SUBSTITUTE(NoBs,"c","") Var NoDs = SUBSTITUTE(NoCs,"d","") Var NoEs = SUBSTITUTE(NoDs,"e","") Var NoFs = SUBSTITUTE(NoEs,"f","") RETURN NoFs
Rinse and repeat for all characters.
Ah, that's great...I'm was just being lazy!! Thanks, Tom
NoText =
Var TempVar =[TextStringFromLookup or some such thing] Var NoAs = SUBSTITUTE(TempVar,"A","") Var NoBs = SUBSTITUTE(NoAs,"B","") Var NoCs = SUBSTITUTE(NoBs,"C","") Var NoDs = SUBSTITUTE(NoCs,"D","") Var NoEs = SUBSTITUTE(NoDs,"E","") Var NoFs = SUBSTITUTE(NoEs,"F","") Var NoGs = SUBSTITUTE(NoFs,"G","") Var NoHs = SUBSTITUTE(NoGs,"H","") Var NoIs = SUBSTITUTE(NoHs,"I","") Var NoJs = SUBSTITUTE(NoIs,"J","") Var NoKs = SUBSTITUTE(NoJs,"K","") Var NoLs = SUBSTITUTE(NoKs,"L","") Var NoMs = SUBSTITUTE(NoLs,"M","") Var NoNs = SUBSTITUTE(NoMs,"N","") Var NoOs = SUBSTITUTE(NoNs,"O","") Var NoPs = SUBSTITUTE(NoOs,"P","") Var NoQs = SUBSTITUTE(NoPs,"Q","") Var NoRs = SUBSTITUTE(NoQs,"R","") Var NoSs = SUBSTITUTE(NoRs,"S","") Var NoTs = SUBSTITUTE(NoSs,"T","") Var NoUs = SUBSTITUTE(NoTs,"U","") Var NoVs = SUBSTITUTE(NoUs,"V","") Var NoWs = SUBSTITUTE(NoVs,"W","") Var NoXs = SUBSTITUTE(NoWs,"X","") Var NoYs = SUBSTITUTE(NoXs,"Y","") Var NoZs = SUBSTITUTE(NoYs,"Z","") RETURN Trim(NoZs)
Here's a cut and paste if anyone wants it.
Sorry man, you might have better luck in "M" in your Power Query, if I come across something better, I'll let you know.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |