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

SivaMani's avatar
SivaMani
Resident Rockstar
8 years ago

Hi Anonymous,

 

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

 

Thanks,

Siva

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

hi SivaMani

 

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

  • 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