Forum Discussion
How to sort area codes using DAX
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.
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.
3 Replies
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.
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) ?
