Forum Discussion
Format Text Column as Phone Number in same column only using Power Query
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?
hello, Anonymous
= Table.TransformColumns( #"Previous Step", {"Unformatted Phone Number Column", (x) => Number.ToText(Number.From(x), "(000) 000-0000")} )
3 Replies
hello, Anonymous
= Table.TransformColumns( #"Previous Step", {"Unformatted Phone Number Column", (x) => Number.ToText(Number.From(x), "(000) 000-0000")} )Hi Anonymous
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 GorillaThank you both! Both good options, but I went with AlienSx since it seems a little cleaner