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"
KNP I don't want to exclude, just filter
Sorry, I want If it doesn't have this format then return "not passport"
smpa01Community Champion
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"- smpa01
Anonymous did you have a chance to look into the solution I provided?
- KNP
Super User
Makes sense, can you provide examples of the ones that don't match this pattern?
Are they always a very different pattern?
KNP has several different formats in this field, other examples have only numbers like these("382098110","8","111111111"), others with more letters that do not apply to passport ("47398183X","C4CW0CPGZ") and with special characters("34.103.927-5")
