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
ZhangKun's avatar
ZhangKun
Icon for Super User rankSuper User
2 years ago
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