Dashboards
Power Pivot
June 25, 2016
I have a file where the SSN is missing the dashes and the leading zeros. If I create a new column with power query, what is the correct formula to format the numbers to the 000-00-0000 format?
Examples:
I need 1234567 to become 001-23-4567
12345678 to become 012-34-5678
123476789 to become 123-45-6789
Thank you for your assistance.
Trusted Members
December 20, 2019
Dashboards
Power Pivot
June 25, 2016
Hi Mynda and Purfleet,
Thank you for your responses. The SSN is the primary key between two files I am joining through Power Query. (One is already formatted, the other is not.) I was hoping I could reformat this within the query so that the person using the tool wouldn't have to do any formatting themselves.
So, is it just not possible in Power Query? I attempted something similar to what Purfleet posted, but it didn't work. I know the Power Query language isn't the same as a regular Excel formula.
Again, thank you for your help. 🙂
July 16, 2010
Hi Pam,
If you want the field to remain a number, then you can't format it in Power Query. If you're happy to convert it to text then you can use Pad.Start to get the correct number of characters, then split the text and insert the hyphens. See file attached.
Mynda
Answers Post
1 Guest(s)