Forum Discussion
Solved
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,...
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.
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. |
