Some Phone numbers come in with hyphens and some do not

[wpf_]

I am importing from dynamics365 data source.  In the data source, all phone numbers has the format xxx-xxx-xxxx, but when it's imported into BI, some become xxxxxxxxxx, and some (xxx) xxx-xxxx.  Most retained the format.  The data type in the data source is phone number, but i guess because BI does not have the format it automatically converted it to type text.  

What's the best way to convert those back to the xxx-xxx-xxxx format?  Thanks.

[v-janeyg-msft · Accepted solution]

Hi, @wpf_ 

According to your description, I think you can use custom format strings:###-###-#### in Power BI Desktop.

Like this: 

If there are numbers and - in the column, you can first unify them into numbers then format in PQ.

Like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[wpf_]

@v-janeyg-msft I folowed your steps but when i try to access it from a measure i get this msg:

[v-janeyg-msft]

@wpf_   The method I provide is only applicable to column because measure can't be seen in the model view and will change as the context changes, it can't be fixed. Have you tried?

Best Regards,
Community Support Team _ Janey

[wpf_]

@v-janeyg-msft 

I got it working now by following your above post, converting the type of whole number then doing a custom formatting.  The error that i was having was because I still had some unclean data in there ex: ), and spaces.  Once i cleaned those up i just used custom formatting.  

But one thing I don't really get is why do I have to conver to whole number first?  Why can't I custom format with text type? 

[v-janeyg-msft]

@wpf_  Hello

Custom format strings follow the VBA style syntax. Types like date and numbers can be formatted.

Check this:

Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs

I don't know much about VBA. But I think you can understand it this way: Text type is more complicated, it can contain numbers, decimal points and letters, so it is difficult to achieve uniformity.

Best Regards,

Community Support Team _ Janey

[wpf_]

@v-janeyg-msft ,

I followed your steps and when accessing it from a measure I am gtting this message.  

[smpa01]

@wpf_  please try this

let
   
   regex=let   fx=(input)=>
    Web.Page(
        "<script>
            var x='"&input&"'; // this is the input string for regex
            var b=x.match(/\d{3}/gm).toString().replace(/,/gm,'-');  
            document.write(b);
        </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text] in fx,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1M7ewVIrVAfN0gVxdGF8DKKAJF4gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each regex([Column1]))
in
    #"Added Custom"

https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p...

[wpf_]

@smpa01 

Not sure if i am ready for javascript in BI yet. 

[Samarth_18]

Hi @wpf_ ,

You can create a custom column like this:-

Column = FORMAT(Phone_number[Phoneno],"###-###-####")

output:-

Thanks,

Samarth

[wpf_]

@Samarth_18 

I can't use format function in calculated column because i am using directquery

[Samarth_18]

@wpf_ ,Okay then you can create a measure with above code.