Cleaning UK Postcode data

[adamlang]

Hi there,

Does anyone have any experience cleaning UK postcodes is Power BI, Power Query, DAX? Maybe there's some standard code that could be used?

I have a list of client postcodes that need to link to other tables to be categorised. However, some of the data is a bit messy:

- The main issue is some postcodes have a double space in the middle where there should only be one. e.g. AA1  1AA should be AA1 1AA.

- Other issues include trimming out spaces at the end, ensuring capitalisation, removing charaters that should not be there - , etc. but that's relatively simple with Trim and Clean.

- a more complex issue is that some postcodes are missing the space entirely. Its not many however and I might be able to live with it This will be hard to resolve because uk postcodes come in different formats:

I won't have any spaces at the end, but getting the space in the middle, in the right place, where non exists already, seems quite hard.

I wondered, does anyone have some standard code for cleaning UK Postcodes, seems like quite a common task I would think?

Or can at least suggest how to remove the double space in the middle, and replace with only a single space.

Many thanks,

Adam

[HotChilli · Accepted solution]

to remove the double space in the middle - Using Power Query. Right-click, Replace Values. Enter 2 spaces, replace with 1 space.

--

Maybe you can solve all problems by splitting column by 3 characters on right.  Clean and trim all data. Then combine columns with a space in between.  These are all done with the interface

[adamlang]

@HotChilli 

Thanks! The Replace values worked a treat. Just like find and replace in excel.

I'll see if I can get other other solution to work over the next day or so.

Thanks.