Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
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 @edumach ,
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.
@edumach 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
@smpa01 - I assumes this refreshes ok in the service and not just the desktop?
@edumach - 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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
@KNP - I assumes this refreshes ok in the service and not just the desktop? - yes Sir, it would refresh without trouble in service; I run several regex on my premium workspace both in DF and dataset
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Sorry, I want If it doesn't have this format then return "not passport"
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"
@edumach did you have a chance to look into the solution I provided?
Makes sense, can you provide examples of the ones that don't match this pattern?
Are they always a very different pattern?
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
@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")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.