UK Post Codes

[Anonymous]

Hi there,

I have some data that includes full post code, but I'm trying to find a way to just pull the postal area (either first letter or first 2 letters) into a new column.

I've looked at LEFT functions but couldn't figure out a way to do both.

Any ideas would be very much appreciated.

[SivaMani]

Hi @Anonymous,

Use Split column option (Edit Quries -> Split Column)

 

Regards,

Siva

[Anonymous]

This is a great option, but I want to leave the original data in one column, and then split into another, like below.

POSTCODEold       POSTCODEnew

DW1 2ZZ               DW

A22 2BB                A

X33 5ZZ                X

PB4 0XX                PB 

[SivaMani]

Simply create the duplicate column (Right click on POSTCODEold -> Duplicate Column). Split the column and delete the unwanted column.

That's it

[SivaMani]

Hi @Anonymous,

If it is working fine, close the thread by accepting the solution.

Thanks,

Siva

[Anonymous]

hi @SivaMani

This doesnt solve the problem I have but is a great tool to use for other issues.

[MarcelBeug]

Alternative solution:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "POSTCODEnew", each List.First(Text.SplitAny([POSTCODEold],"0123456789")))
in
    #"Added Custom"

[Anonymous]

Thank you @MarcelBeug

I will try this in a little while and see what happens.

Really appreciate the reply.

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You can also achieve this via creating a calculated column using DAX:

POSTCODEnew =
IF (
    RIGHT ( LEFT ( 'UK Post Code'[POSTCODEold], 2 ), 1 )
        IN { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" },
    LEFT ( 'UK Post Code'[POSTCODEold], 1 ),
    LEFT ( 'UK Post Code'[POSTCODEold], 2 )
)

Best regards,
Yuliana Gu

[Jsummers]

Hi @v-yulgu-msft 

How can I do this in a measure please?  My company don't allow me to create columns in certain datasets we use.

Thanks

[Anonymous]

hi  @v-yulgu-msft

That has worked perfectly. Very much appreciated Yuliana.

Have a great day.

[DaveARoberts]

Here's another option....

Post Code Area =

   VAR PCD = [Post Code District]

   VAR C2 = MID(PCD,2,1)

RETURN

SWITCH(

   TRUE(),

      SEARCH(C2,"0123456789",1,0) >= 1,LEFT(PCD,1),

      LEFT(PCD,2)

)

[MarcelBeug]

Still, Power Query would be the more appropriate environment for this kind of transformations.