Forum Discussion

JohnHarker's avatar
JohnHarker
New Member
2 years ago

Extracting a sequence in Power Query

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 a...
Need Help
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
2 years ago

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"