Notifications
Clear all
Power Query
4
Posts
2
Users
0
Reactions
718
Views
Topic starter
Good evening.
I have a zip code nightmare file.
Some zip codes contain the plus four added (1st example), some fail to include a leading zero (2nd example), and some contain just the 5 digits (last example). I need to have them with just the 5 digits. Looking forward to see how the great minds resolve this one 😉
| What it is… | What it needs to be… |
| 236013428 | 23601 |
| 80811912 | 08081 |
| 37924 | 37924 |
Posted : 05/10/2021 10:19 pm
Topic starter
Attachment included
Posted : 05/10/2021 10:21 pm
Make sure the Zip field loads as text, then you can use something like:
= Table.AddColumn(#"Changed Type", "CleanZip", each if Text.Length([Addressee Zip]) <=5 then Text.PadStart([Addressee Zip], 5, "0") else Text.PadStart(Text.Start([Addressee Zip], Text.Length([Addressee Zip])-4), 5, "0"))
Posted : 06/10/2021 3:45 am
Topic starter
Thank you for the reply and insight. This worked very well.
Posted : 09/10/2021 3:00 pm
