Power Query: IMEI Check

A (somewhat) convoluted bit of M code to calculate the validity of an IMEI number (yes, this is related to my job, how’d ya guess?).

Create a new function in the Power Query Window, and call it IMEICheck; open the code in the Advanced Editor, and replace the existing code with the code below.

Parameter is simply the IMEI text (or IMEI-containing column of an Excel Sheet or Data table). Returns either one of 4 values, based on the input column value:

• [input] – works best when used with a column that contains a quick pre-check (15 characters, numbers only) and returns a text-value indicating the quickly scanned invalidity of the entered number;
• Invalid – does a quick scan of the basics, and it failed;
• Failed checksum – complete calculation based on Luhn Checksum – and it failed;
• OK – the scanned IMEI is valid.

Even though the code is a mess of repeating items, it’s still quite a bit faster than several other methods I’ve seen which incorporate splitting the IMEI into separate columns, then calculating THOSE, and THEN calculating the checksum, AND THEN removing all those columns again, and adding the checksum result in a final, new column.

Oof.

Note that the numbers 35 and 86 are based on IMEI numbers used for specific phone makes, as available in The Netherlands. Adjust/add checks as required!

let
    IMEICheck = (input as text) as text =>
    if (try Number.FromText(Text.Trim(input)) otherwise -1) = -1 
    then input
    else 
        if Text.Length(Text.Trim(input)) <> 15 or (Text.StartsWith(Text.Trim(input), "35") = false 
            and Text.StartsWith(Text.Trim(input), "86") = false)
        then "Invalid"
        else if Number.Mod(
            Number.FromText(Text.Middle(Text.Trim(input), 0, 1)) + 
            Number.FromText(Text.Middle(Text.Trim(input), 2, 1)) + 
            Number.FromText(Text.Middle(Text.Trim(input), 4, 1)) + 
            Number.FromText(Text.Middle(Text.Trim(input), 6, 1)) + 
            Number.FromText(Text.Middle(Text.Trim(input), 8, 1)) + 
            Number.FromText(Text.Middle(Text.Trim(input),10, 1)) + 
            Number.FromText(Text.Middle(Text.Trim(input),12, 1)) +
            Number.FromText(Text.Middle(Text.Trim(input),14, 1)) +
            (if Number.FromText(Text.Middle(Text.Trim(input), 1, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input), 1, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input), 1, 1)) * 2) - 9)) + 
            (if Number.FromText(Text.Middle(Text.Trim(input), 3, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input), 3, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input), 3, 1)) * 2) - 9)) + 
            (if Number.FromText(Text.Middle(Text.Trim(input), 5, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input), 5, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input), 5, 1)) * 2) - 9)) + 
            (if Number.FromText(Text.Middle(Text.Trim(input), 7, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input), 7, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input), 7, 1)) * 2) - 9)) + 
            (if Number.FromText(Text.Middle(Text.Trim(input), 9, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input), 9, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input), 9, 1)) * 2) - 9)) + 
            (if Number.FromText(Text.Middle(Text.Trim(input),11, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input),11, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input),11, 1)) * 2) - 9)) + 
            (if Number.FromText(Text.Middle(Text.Trim(input),13, 1)) * 2 < 10 
                then (Number.FromText(Text.Middle(Text.Trim(input),13, 1)) * 2) 
                else ((Number.FromText(Text.Middle(Text.Trim(input),13, 1)) * 2) - 9)), 10) = 0 
        then "OK"
        else "Failed checksum"
in
    IMEICheck
Code language: JavaScript (javascript)

Additional resources regarding the Luhn MOD 10 check:

Introduction of Luhn algorithm (modulo 10 algorithm) and IMEI checking of C# code

And an interesting bit of coding to see how it works:

https://sndeep.info/en/tools/checksum_calculator