Formatting Postcodes

[JWorthy]

Hello,

I have a .csv of delivery information with postcodes that I need to format correctly before I import into Power BI.

Below are the different incorrect ways that postcode information can come to me incorrect and how I'd like to fix the postcodes.

Record #	Postal Code_Incorrect (how i receive them)	Postal Code_Fixed (what I want to transform them into).
1	A99AA	A9 9AA
2	A999AA	A99 9AA
3	AA99AA	AA9 9AA
4	AA999AA	AA99 9AA
5	A9A9AA	A9A 9AA
6	AA9A9AA	AA9A 9AA
7	99999	99999

How can I correctly format the postcodes?

I'm really new to Power BI so in any response please assume I'm a total idiot - as basic instructions as possible please.

Thanks for your help 🙂

J

[amitchandak]

Try a new column like

new column =left(table[Postal Code],search("9AA",table[Postal Code],1,len(table[Postal Code]))) & " "& if(search("9AA",table[Postal Code],1,-1)>0,"9AA","")

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[FrankAT · Accepted solution]

Hi,

1. Open Power Query editor.
2. Select column 'Postal Code_Incorrect (how i receive them)'.
3. Change data typ to text.
4. Tab Add Column > Column From Examples
5. Enter in the first row the postal code A9 9AA, press Enter.
6. Enter in the second row the postal code A99 9AA, press Enter.
7. Ok.
8. Tab Transform > Format > UPPERCASE.
9. Done!

Regards FrankAT

[CerysWakeman]

Hi Frank,

Thank you for your example it was helpful. When I did this to my dataset the rule it created only created an if>then clause for all the postcodes containing errors.

This means I have to manually check when I append data. Is this because I had too few erroneous examples to work from? Would it be better to remove all spaces in the correct postcodes and then complete?

If not do you know how I could create a more substantial rule? I think for my data a rule where there's a space before the last 3 characters would fix all errors.

Screenshot added with fictional values to illustrate.