Format Text Column as Phone Number in same column only using Power Query

[GraysonBishop23]

Hello,

I've got a text column with phone numbers in the format "##########" and I want to convert it to the format "(###) ###-####". I've seen another thread that talks about splitting the column and recombining into another column, but I want to avoid that many transformations. I can't use DAX (I'm consuming this dataset elsewhere).

I might be trying to be too clever about it, but I feel like this is possible using a combination of Table.TransformColumns, Text.Format, and Text.ToList like this:

= Table.TransformColumns(#"Previous Step", {{"Unformatted Phone Number Column", each Text.Format("(#{1}#{2}#{3}) #{4}#{5}#{6}-#{7}#{8}#{9}#{10}",Text.ToList(_), type text}}))

I feel like this is close, but I can't quite work out the syntax. Any tips?

[GraysonBishop23]

Thank you both! Both good options, but I went with @AlienSx since it seems a little cleaner

[AlienSx · Accepted solution]

hello, @GraysonBishop23 

= Table.TransformColumns(
    #"Previous Step", 
    {"Unformatted Phone Number Column", 
     (x) => Number.ToText(Number.From(x), "(000) 000-0000")}
)

[Daryl-Lynch-Bzy]

Hi @GraysonBishop23 

You could try the following transformation:

= Table.TransformColumns( #"Previous Step" , {{ "Custom", each "(" & Text.Range( _ , 0, 3 ) & ") " & Text.Range( _ , 3, 3 ) & "-" & Text.Range( _ , 6, 4 ) , type text}})

This is a good resource for learning about all the Text functions in Power BI.

Text Functions in Power Query M (150+ Examples) - BI Gorilla