Get The Year Of Registration From A UK Vehicle Number Plate

[SachinC]

Hi all,

I have a database column with VRN, i.e. AA67BBC and I wish to extract the year of registration.  

For example, AA67BBC is a 2017/2018 car.  I need the value of 2017/2018 - i.e. the year.

Here is a lookup, registrations can be old style and new style.

e.g. Old Style: D803KKR which equates to: 1986

e.g. New Style: AA67BBC equates to: 2017/2018

Old Style
---------

YEAR OF RELEASE PREFIX
1983 A
1984 B
1985 C
1986 D
1987 E
1988 F
1989 G
1990 H
1991 J
1992 K
1993 L
1994 M
1995 N
1996 P
1997 R
1998 S
1999 T
1999 V
2000 W
2000 X
2001 Y

New Style
---------
YEAR OF RELEASE 1st MARCH to
31st AUGUST 1st SEPTEMBER to
28/29th FEBRUARY
2001/02 51
2002/03 02 52
2003/04 03 53
2004/05 04 54
2005/06 05 55
2006/07 06 56
2007/08 07 57
2008/09 08 58
2009/10 09 59
2010/11 10 60
2011/12 11 61
2012/13 12 62
2013/14 13 63
2014/15 14 64
2015/16 15 65
2016/17 16 66
2017/18 17 67
2018/19 18 68
2019/20 19 69
2020/21 20 70
2021/22 21 71

The above is taken from: Vehicle Registration Years (nationalnumbers.co.uk)

Help!!!

Thanks!

[lbendlin]

I don't think your table is complete.  This is more like it (call it "Registrations"):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdTLilVLEEXRf6m24M6I2Dszm77FF6LiVUT8/7+4dWqFtWZL7JxyEhS1ziB//br78+zuyd14ev9vX3n7GE+zf/j95D4/dy7mUn7hfDKfyi+dL+ZL+ZXzZJ7Kr50X81J+47yZt/LbxzwP5PsfHvI758E8lN87B3Mof3DmarNX++jM1Wav9smZq81e7bMzV5u92hdnrjZ7ta/OXG32at+cudrs1b4/5sXVVq/2w5mrrV7tpzNXW1rt2R9nrra02nNkrra02gtkrra02ktkrra02itkrra02mtkrra02htkrra02lvnzdW2VnuHzNW2VnuPzNW2VvuAzNW2VvuIzNW2VvuEzNW2VvuMzNW2VvuCzNW2VvuKzNW2VvuGzNW2Vvv+7/zf3xzH4VEffrjlH//OP5EHszY/b/+3neNprNtnTX7cvoZUvX1cfat6hm8Dt/o+jvRt4lb1TN+mbvfts76so3xbuFU9y7eF36tv8jh9e+JW9Tx9e+JWX/Nx+fbCrep5+fbCrQwc07cTt6rn9O3E3ysgx/Ltwq3quXy78HvF49i+3bhVPbdvt2+HcIzj8fbhY992vY7H24f691ZyxvDtwK3qZVdj+O8dkjPsasBV18uuBlwNyRl2NeCq62VXI3ErOcOuBlx1vexqwNWQnGFXA666XnY1Tvy9kjPsasBV18uuBlwNyRl2NeCq62VXY+JWcoZdDbjqetnVgKshOcOuBlx1vexqbP+9ITlhVwFXXaddBVyF5IRdBVx1nXYVeK9CcsKuAq66TrsKuArJCbsKuOo67SrwXoXkhF0FXHWddhVwFZITdhVw1XXaVeC9CskJuwq46jrtKuAqJCfsKuCq67SrwHsVkhN2FXDVddpVwFVITthVwFXXaVeB9yolJ+0q4arrsquEq5SctKuEq67LrhLvVUpO2lXCVddlVwlXKTlpVwlXXZddJd6rlJy0q4SrrsuuEq5SctKuEq66LrtKvFcpOWlXCVddl10lXKXkpF0lXHVddpV4r1Jy0q4Srrouu0q4SslJu0q46rrsKvFeleSUXRVcdd12VXBVklN2VXDVddtV4b0qySm7Krjquu2q4Kokp+yq4KrrtqvCe1WSU3ZVcNV121XBVUlO2VXBVddtV4X3qiSn7Krgquu2q4Krkpyyq4KrrtuuCu9VSU7ZVcFV121XBVclOWVXBVddt10V3qtTck67OuHqVn//Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, From = _t, To = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type date}, {"To", type date}})
in
    #"Changed Type"

Once this is in place you can then test your submitted plates against all three criteria

- Is the last digit a letter and the second to last a number? => suffix

- Is the first digit a letter and the second a number ? => prefix

- are the first two digits letters and the next two digits numbers? => millenium

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45W8vF1NDT0VorViVZysTAw9vYOArMdHc3MnZyclWJjAQ==", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"License Plate" = _t]
  ),
  #"Added Custom" = Table.AddColumn(
    Source,
    "Pattern",
    each
      if List.Contains({"A" .. "Y"}, Text.End([License Plate], 1))
        and List.Contains({"0" .. "9"}, Text.Start(Text.End([License Plate], 2), 1))
      then
        "_" & Text.End([License Plate], 1)
      else if List.Contains({"A" .. "Y"}, Text.Start([License Plate], 1))
        and List.Contains({"0" .. "9"}, Text.End(Text.Start([License Plate], 2), 1))
      then
        Text.Start([License Plate], 1) & "_"
      else if List.Contains({"A" .. "Y"}, Text.Start([License Plate], 1))
        and List.Contains({"A" .. "Y"}, Text.End(Text.Start([License Plate], 2), 1))
        and List.Contains({"0" .. "9"}, Text.End(Text.Start([License Plate], 3), 1))
        and List.Contains({"0" .. "9"}, Text.End(Text.Start([License Plate], 4), 1))
      then
        Text.End(Text.Start([License Plate], 4), 2)
      else
        "not recognised"
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Added Custom",
    {"Pattern"},
    Registrations,
    {"Code"},
    "Registrations",
    JoinKind.LeftOuter
  ),
  #"Expanded Registrations" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Registrations",
    {"From", "To"},
    {"From", "To"}
  )
in
  #"Expanded Registrations"

Which results in

[SachinC]

Hi Mate,

You are a genius - but please can you give me steps (step by step instructions) on how I can use your code against my table?

Thanks!

[lbendlin]

That's totally up to you. Attached is an example that implements the code as a function that you can call from your list of plates.