Extracting a sequence in Power Query

[JohnHarker]

I have a long column of data where I need to extract a specific sequence. I am looking for the format ####-##A####. The problem is there is no pattern as to where this sequence apprears in the cell and the letter could be any letter.

I need to find the sequence and then copy it to a new column, is there a way to do this with a custom column in Power Query?

Thank you

[v-stephen-msft]

I hope you’re doing well. I wanted to check in and see if the issue you mentioned has been resolved. If it has, could you please mark the response that helped you as the solution? This will make it easier for other users to find the answer quickly.

Thank you for your cooperation!

Best regards,

Stephen Tao

[Ahmedx]

and try this

let
f=(w)=> try  Splitter.SplitTextByRanges(
    {{
         [
             a = List.Transform( {0..9},(x)=>  "-"& Text.From(x)),
             b = List.Transform(a,(x)=>Text.PositionOf(w,x,2)),
             c = List.Sort( List.Combine(b),1){1}+1][c],12}}
             )(w){0} otherwise "",


        Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"ddddddddd"}
    }, type table [Sample = text ]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each f([Sample]))
in
    #"Added Custom"

[Ahmedx]

pls try this

let
f=(x)=> Text.AfterDelimiter( Text.Split(x,"-Congo"){0},"-",{1,RelativePosition.FromEnd}),
    Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"}
    }, type table [Sample = text ]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each f([Sample]))
in
    #"Added Custom"

 

[PwerQueryKees]

Awesome @AlienSx ...

[AlienSx]

thanks,  ! To be honest, I've made a mistake - there must be 12 (not 11) in Text.Range(txt, find_position + 1, 11)

@PwerQueryKees

[PwerQueryKees]

Hilarious! Still a very creative and generic way to tackle OP's problem....

[wdx223_Daniel]

NewStep=Table.AddColumn(YourTable,"Extract",each List.RemoveNulls(List.Transform({0..Text.Length([LongTextColumn])-12},(x)=>let a=Text.Range([LongTextColumn],x,11),b=Text.SplitAny(a,{"-","A".."Z"}) in if List.Count(b)<>3 then null else  if List.AllTrue(List.Transform({0..2},(y)=>Text.Remove(b{y},{"0".."9"})="" and Text.Length(b{y})={4,2,4}{y}) then a else null){0}?)

[AlienSx]

let
    chars = List.Transform({"a".."z", "A".."Z"}, (x) => {x, "A"}),
    nums = List.Transform({"0".."9"}, (x) => {x, "#"}),
    replacements_list = List.Buffer(List.Combine({chars, nums})),
    pattern = "-####-##A####-",
    find_pattern = (txt) =>
        [lst = Text.ToList(txt), 
        rpl = List.ReplaceMatchingItems(lst, replacements_list),
        t = Text.Combine(rpl), 
        find_position = Text.PositionOf(t, pattern, Occurrence.First), 
        res = if find_position <> -1 then Text.Range(txt, find_position + 1, 11) else null][res],
    // edit Source to get your original data
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    new_col = Table.AddColumn(Source, "pattern", (x) => find_pattern(x[column_name]), type text)
in
    new_col

[ZhangKun]

let
    Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"}
    }, type table [Sample = text ]),
    fx = (str, pat) => Web.Page("<script>document.write('"& str &"'.match(/" & pat & "/g))</script>")[Data]{0}[Children]{0}{[Name="BODY"]}[Children][Text]{0}, 
    ExtractPattern = Table.AddColumn(Source, "Custom", each fx([Sample], "\d{4}-\d{2}[A-Z]\d{4}"))
in
    ExtractPattern

[m_dekorte]

Hi @JohnHarker ,

Give something like this a go, its a bit verbose but does the trick.

let
    Source = Table.FromRows({
        {"519-1-16800 Work in progress-WHQ projects-Branch (Travaux en cours - Immobilisations)-2234-10S1460-Congo (Kinshasa) Lubumbashi Branch"},
        {"519-1-12910 Prepaid expenses-Service contracts-2294-11S4992-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"},
        {"519-1-21510 Accrued liabilities payable-Service contracts-2294-11S4993-Congo (Kinshasa) Elephants Branch Annex-Contract Support\\Contract"}
    }, type table [Sample = text ]),
    ExtractPattern = Table.AddColumn(Source, "Custom", each  
        Text.Combine( 
            List.Select( 
                List.TransformMany( 
                    Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"-"})([Sample]), 
                    each Splitter.SplitTextByCharacterTransition({"-"}, (c) => not List.Contains({"0".."9"}, c))(_),
                    (a, b) => Text.TrimEnd( Text.TrimStart(b, "-"), "-")
                ), (x)=> List.AllTrue(
                    {
                        Text.Length(x) =12, 
                        Text.PositionOf(x, "-") =4, 
                        Text.PositionOf(x, Text.Select(x, {"A".."Z"})) =7
                    }
                )
            ), ", "
        )              
    )
in
    ExtractPattern

with this result

I hope this is helpful