undefined

[edumach]

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]

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.

[smpa01]

@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

https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data-ex...

https://community.powerbi.com/t5/Community-Blog/Using-JavaScript-in-power-query-for-regex-Part2/ba-p...

[KNP]

@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.

[smpa01]

@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

[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.

[edumach]

@KNP I don't want to exclude, just filter

[edumach]

Sorry, I want If it doesn't have this format then return "not passport"

[smpa01 · Accepted solution]

@edumach 

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]

@edumach  did you have a chance to look into the solution I provided?

[KNP]

Makes sense, can you provide examples of the ones that don't match this pattern?

Are they always a very different pattern?

[edumach]

@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")