Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

UK Post Codes

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Alternative solution:

 

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "POSTCODEnew", each List.First(Text.SplitAny([POSTCODEold],"0123456789")))
in
    #"Added Custom"
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thank you MarcelBeug

 

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

 

Really appreciate the reply.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    hi  v-yulgu-msft

     

    That has worked perfectly. Very much appreciated Yuliana.

     

    Have a great day.

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

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

  • DaveARoberts's avatar
    DaveARoberts
    Frequent Visitor
    7 years ago

    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)
    )
  • Jsummers's avatar
    Jsummers
    Helper I
    2 years ago

    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