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

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"