Forum Discussion
Extract 7 digit number after specific letter in Power Query
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
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.
2 Replies
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.
