Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to Solution.
hello, @GraysonBishop23
= Table.TransformColumns(
#"Previous Step",
{"Unformatted Phone Number Column",
(x) => Number.ToText(Number.From(x), "(000) 000-0000")}
)
Thank you both! Both good options, but I went with @AlienSx since it seems a little cleaner
hello, @GraysonBishop23
= Table.TransformColumns(
#"Previous Step",
{"Unformatted Phone Number Column",
(x) => Number.ToText(Number.From(x), "(000) 000-0000")}
)
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.