Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

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

Need Help

smpa01

4 years ago

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"

Anonymous

Not applicable

4 years ago

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

smpa01

Community Champion

4 years ago

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
Community Champion
4 years ago
Anonymous did you have a chance to look into the solution I provided?