Sep 17 2020 08:09 AM
I am trying to find a way to simplify this code since it annoying to add nested characters and I think it inefficient when it comes to performance
The point is, to leave only number 0-9 characters in the new column, because the people who input data there often make mistakes, and later format it as (###)###-####
My code:
Sep 17 2020 11:13 AM
I'm not familiar with DAX per se, but have the impression that many Excel features operate there as well.
So can you use Data Validation to ensure that what is entered is a number, 10 digits long, entered solely as numbers, and then let custom formatting take care of rendering it as (123) 456-7890. That's what I'd do in Excel. The user would get an error message if they tried anything else.
Sep 17 2020 11:17 AM
I get this data as an external feed directly in my Power Pivot, I cannot control the input, in fact I don't even know who are the people who does that input so what I need is a way to fix it in DAX
Sep 17 2020 11:23 AM
I'm going to defer to others for a fix of that code.
You've got a difficult situation, from your description, and it may well be impossible to do what I'm going to suggest, but you surely realize that what you're doing is fixing something after it got broken.
I wonder if there's any way--may take some time--to get to the front of the process and prevent the breakage in the first place. It's generally cheaper in the long run to design a process so as to prevent problems than it is to recover from them.
Sep 17 2020 12:01 PM
Hi @Ocasio27,
I don't know much about DAX but if you want to give it a shot with VBA, you can use this function.
Function PhoneNumber(sWord As String)
Dim sChar As String
Dim x As Integer
Dim sTemp As String
sTemp = ""
For x = 1 To Len(sWord)
sChar = Mid(sWord, x, 1)
If Asc(sChar) >= 48 And _
Asc(sChar) <= 57 Then
sTemp = sTemp & sChar
End If
Next
PhoneNumber = "(" & Left(sTemp, 3) & ") " & Left(Right(sTemp, 7), 3) & "-" & Right(sTemp, 4)
End Function
So if your "bastardized" number is in cell A1, you can type in cell B1
=PhoneNumber(A1)
You will probably want to add some sort of verification that there is 10 digit in the phone number though otherwise this won't work properly.
Sep 17 2020 12:09 PM
Sep 17 2020 01:43 PM
SolutionTo my knowledge in DAX there is no equivalent for sequential SUBSTITUTE() in such cases. As a comment, these days such transformation usually are doing in Power Query with M-script, that will be one short string.
As for DAX, I'd adjust formula a bit like
=
VAR phoneNumber=
IF(
ISBLANK([CELULAR]),
[TELEFONO],
[CELULAR]
)
VAR cleanText=
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
phoneNumber, "-", ""
), "(", ""
),")",""
)," ",""
),"/",""
),"\",""
)
RETURN cleanText
Sep 17 2020 04:15 PM
Thank you.
In this case, is there any way to remove non numerical characters? I am trying to extract only 0-9 characters
Sep 18 2020 02:03 PM
Unfortunately I don't know other ways with DAX-only but modifications of above formula.
Sep 21 2020 05:34 PM
There is a variant. Not sure how it'll be from performance point of view, however. Let add helper table ID to the data model
With it calculated column B
could be as
=VAR source=Table2[A]
VAR charactersTable=
FILTER (
SELECTCOLUMNS (
'ID',
"Value",'ID'[ID],
"mchar",MID(source,'ID'[ID],1),
"mcode", UNICODE(MID(source,'ID'[ID],1))
),
[mcode] <58 && [mcode] >47
)
VAR Result= CONCATENATEX(charactersTable, [mchar])
Return Result
(48 is ascii code for zero, and 57 for 9)
Sep 17 2020 01:43 PM
SolutionTo my knowledge in DAX there is no equivalent for sequential SUBSTITUTE() in such cases. As a comment, these days such transformation usually are doing in Power Query with M-script, that will be one short string.
As for DAX, I'd adjust formula a bit like
=
VAR phoneNumber=
IF(
ISBLANK([CELULAR]),
[TELEFONO],
[CELULAR]
)
VAR cleanText=
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE (
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
phoneNumber, "-", ""
), "(", ""
),")",""
)," ",""
),"/",""
),"\",""
)
RETURN cleanText