Excel BI Challenge 342 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "SA IDs", "ID"),
    #"Sp;it1-6" = Table.SplitColumn(Source, "ID", Splitter.SplitTextByPositions({0, 6}, false), {"Date", "ID.2"}),
    #"Split7-10" = Table.SplitColumn(#"Sp;it1-6", "ID.2", Splitter.SplitTextByPositions({0, 4}, false), {"SSSS", "ID.2.2"}),
    Split11 = Table.SplitColumn(#"Split7-10", "ID.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Citizen", "ID.2.2.2"}),
    Split12 = Table.SplitColumn(Split11, "ID.2.2.2", Splitter.SplitTextByPositions({0, 1}, false), {"Rand", "Checksum"}),
    Split13 = Table.SplitColumn(Split12, "Date", Splitter.SplitTextByRepeatedLengths(2), {"Year", "Month", "Day"}),
    TypeInt64 = Table.TransformColumnTypes(Split13,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"SSSS", Int64.Type}, {"Citizen", Int64.Type}, {"Rand", Int64.Type}, {"Checksum", Int64.Type}}),
    AddtoYear = Table.TransformColumns(TypeInt64, {{"Year", each _ + 1900, type number}}),
    AddTestDate = Table.RemoveRowsWithErrors( Table.AddColumn(AddtoYear, "TestDate", each #date( [Year], [Month], [Day] )), {"TestDate"}),
    FilterSSSS = Table.SelectRows(AddTestDate, each [SSSS] >= 0 and [SSSS] <= 9999),
    FilterCitizen = Table.SelectRows(FilterSSSS, each ([Citizen] = 0 or [Citizen] = 1)),
    FilterRand = Table.SelectRows(FilterCitizen, each [Rand] >= 0 and [Rand] <= 9),
    AddCalcChecksum = Table.AddColumn(FilterRand, "CalcChecksum", each [
        Q = List.Select( Text.ToList( Text.Start( [SA IDs], 12)), each List.Contains( {"0".."9"}, _ )),
        Even = List.Alternate(Q, 1, 1, 0),
        Odd = List.Alternate( Q, 1, 1, 1),
        A = List.Sum( List.Transform(Odd, each Number.From(_))),
        B = Number.From( Text.Combine( Even, "")),
        B2 = List.Transform( Text.ToList( Text.From( 2 * B)), each Number.From(_)),
        C = List.Sum( B2 ),
        D = A + C,
        Z = 10 - Number.Mod(D, 10)
    ][Z]),
    Clean = Table.SelectColumns( Table.SelectRows(AddCalcChecksum, each [Checksum] = [CalcChecksum]), "SA IDs")
in
    Clean