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 information I'm looking for has this format "FR575912"
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"
11 Replies
smpa01Community Champion
Anonymous you can use regex for pattern match
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]), #"Filtered Rows" = Table.SelectRows(Source, each (regex([Data]) <> "null")) in #"Filtered Rows"From here
to here
- KNP
Super User
smpa01 - I assumes this refreshes ok in the service and not just the desktop?
Anonymous - This is probably the best solution if you're happy with the JavaScript approach. The other way to tackle it is to identify that the string has alphas as the first two characters and then confirm the string length. I can put something together if JavaScript regex is not what you're looking for.
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.- KNP
Are you saying there are other records in the Passport column that don't follow this pattern and you want to exclude them?
Need more info.
KNP I don't want to exclude, just filter
