Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |