Forum Discussion

agc1234's avatar
agc1234
New Member
2 years ago

Extracting a Specific string format from a string of text

Good Day Everyone,   I am trying to extract a specific format of: ##a# or (number)(number)(letter)(number)   I need first 4 parts of the serial number. I've attached examples of source data and w...
Need Help
AlienSx's avatar
AlienSx
Super User
2 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4xCgIxEEWvMqQ2kMxMJoldomAhpLERYgqFLRa0Wdn7q5scwPLNPPivVmVNEiaPqu2quhSDhyCGqeO0zPcnlPX1mBaY37AHS+cYkXjo30MsJOy8BLitxqCAxZML/ip+c6xAAgkkfQFRZ+8Cx05WZybNTrprtpR8HPQrSyMtjecfw6q1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial #" = _t]),
    pattern = {false, false, true, false},
    chars = List.Buffer({"0".."9", "A".."Z"}),    
    finder = (t as text) =>
        [lst = List.Buffer(List.Select(Text.ToList(t), (x) => List.Contains(chars, x))),
        gen = List.Generate(
            () => [ch = lst, okay = false, serial = {}],
            (x) => not List.IsEmpty(x[ch]) or x[okay], 
            (x) => 
                [okay = List.Transform(List.FirstN(x[ch], 4), (w) => (try Number.From(w))[HasError]) = pattern,
                serial =  if okay then List.FirstN(x[ch], 4) else {},
                ch = List.Skip(x[ch], if okay then 4 else 1)],
            (x) => Text.Combine(x[serial])
        ),
        z = Text.Combine(List.Select(gen, each _ <> ""), ", ")][z],
    out = Table.AddColumn(Source, "Desired Outcome", (x) => finder(x[#"Serial #"]))
in
    out