Forum Discussion

JWorthy's avatar
JWorthy
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Formatting Postcodes

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...
Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    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

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    CerysWakeman
    New Member
    3 years ago

    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.