Forum Discussion
undefined
i need to create a column that filters only the records that are in the passport column, they have a certain pattern, they have 2 letters at the beginning and a sequence of 6 numbers the informat...
Anonymous
let regex=let fx=(input)=> Web.Page( "<script> var x='"&input&"'; // this is the input string for regex var b=x.match(/[a-zA-Z]{2}\d{6}/gm); // specify the desired regular expression inside string.match() //https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/match document.write(b); </script>"){0}[Data]{0}[Children]{1}[Children]{0}[Text] in fx, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgsyNTe1NDRSitWJVjK2MDKwtDA0NADzLMCkIQyAeSbmxkAFFsYRYJ6ziXO4gXOAexREt4meoYGxnqWRua4pWCAi0tLC3MQUaE4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each let x = regex([Data]) in if x="null" then "not passport" else x) in #"Added Custom"
Hi Anonymous ,
As KNP suggested, please try the following formula to add a custom column:
=if Text.Length([Value])=8 and
Text.Start([Value],2)=Text.Select([Value],{"A".."Z"}) and
Text.Range([Value],2)=Text.Select([Value],{"0".."9"})
then "Yes" else "not passport"
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.