Extract 7 digit number after specific letter in Power Query

[sja133]

Hi

Trying to extract a 7 digit order number from a text string. Order numbers start with O followed by 7 digits. Problem is order number can appear anywhere in the string. Example in table below, have highlighted what we need to extract. 

Any guidance would be appreciated 🙂

RUSH REQUEST: O4977663 Customer 123412 QDE Reference BOOTSTOCK
PFD TEMPLATE : O4977441 Customer 156084 QDA Reference PT987443 
RUSH REQUEST: O4977743 Customer 121411 QDA Reference 91956
RUSH REQUEST: O4977807 Customer 147611 QDA Reference JESSE.SMITH 
URGENT REQUEST FOR DELIVERY PLEASE - O4976974 A/c 127087 
RUSH REQUEST: O4977824 Customer 337216 VDA Reference 4501403367 

[AlB · Accepted solution]

Hi @sja133 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc9BU8IwEIbhv7LTs2LSbLOJt0oXQYspScoM0+HEhBswg/r/rahjEbnv98y7XZf5NkzB86LlEO/BoSXSWsH4/fXtsEtHkLlCmcOiYvBpm45pv0nw4FwM0Y2fs/VNlzWTCiLPm7qMDN8GohwYhRYGe6McGE20pj9TcDL+ySA8y5Ao5R/CSlvoa3MjaDBH0hfzJw6BR2E+i9OviNY/8kv8cWDiPFRcz5bsV9DUXAaG2xOuLSGUd5s+i4Shqy+YHH8blKJcalieNWAhJAql9Cey/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each List.Select(Text.Split([Column1],  " "), 
each Text.Start(_,1) = "O" and Text.Length(_)=8  and Text.Length(Text.Select(Text.End(_,7), {"0".."9"})) = 7)),
    
   #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Result", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[sja133]

Thank you @AlB that worked awesomely. i can study the query now and learn something new 🙂