How to sort area codes using DAX

[Anonymous]

Hi,

I have a bunch of area codes that I want to sort out to make it more presentable in a graph. So for example, I have BR1, BR3, E10, E7, E5, SE9, SW19, WC2B etc. Is there a way for me to sort it out so that at any time the area code is E or SW it sorts it out into another column with the area such as East or South-West? 

Thanks in advance. 

[Anonymous · Accepted solution]

@goncalogeraldes helped me figure out a solution using nested if statements 🙂 

For anyone else struggling, here's an example of what I used: 

= Table.AddColumn(#"Renamed Columns3", "Region", each 
if Text.Start([AreaCode],2) = "SW" then "South-West" else /* for conditions where you need the first 2 letters */
if Text.Start([AreaCode],2) = "SE" then "South-East" else
if Text.Start([AreaCode],2) = "BR" then "Bromley" else
if Text.Start([AreaCode],2) = "WC" then "West-Central" else
if Text.Start([AreaCode],2) = "EC" then "East-Central" else
if Text.Start([AreaCode],2) = "NW" then "North-West" else
if Text.Start([AreaCode],1) = "E" then "East" else /* for conditions where you only need the first letter */
if Text.Start([AreaCode],1) = "N" then "North" else
if Text.Start([AreaCode],1) = "W" then "West" 
else "London" )

If anyone else is doing something similar, I would note that I had issues with putting the single letter statements first as it would lump the double letter regions into the same one. For example, if I put North first, it would lump North-West into the same region. 

[lbendlin]

Please provide more context.  Are you expecting this to be based on geocoding calls, or on the starting characters of the area codes (for example the letters before the first number digit) ?

[Anonymous]

Hi, 

Yes, I'd like to return all the letters before the digits and sort them by region. So at the end instead of having: 

E1 I would have E for a regional breakdown of East 

SW19 -> SW -> South-West

SE9 -> SE -> South-East 

I hope this makes sense. Sorry I'm still quite new to PowerBI 🙂