Forum Discussion
How to return 'TRUE' if string matches certain specific structure
Hi JonSwed ,
This was interesting, yet a bit cumbersome.It is all about pulling the characters from the string after getting them based on their position.
The first thing I did was easy to check on the underscore. The more problamatic one is the Alpha characters which can include anything not numeric, like an underscore. If you paste the code into a blank query in the Advanced Editor, you will see what I did. The first 7 or 8 steps were just to clean up the table. I just did a proof of concept as far as the alpha characters. You will have to add the rest of the alphabet, and lower case if those might be true.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg+NdwyJD3CNLynKLy1OLSqOTyrNK1awVSgpKk1VitWJVgITMGXYJBILCnJSiyGSaYk5xUiyufl52amVxU6piSCDM4tSiuOTE0uK41Py04tRVMc7OirFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","= true","",Replacer.ReplaceText,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","= false","",Replacer.ReplaceText,{"Column1"}), #"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Column1", Text.Trim, type text}}), #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Column1", Text.Clean, type text}}), #"Trimmed Text1" = Table.TransformColumns(#"Cleaned Text",{{"Column1", Text.Trim, type text}}), #"Filtered Rows" = Table.SelectRows(#"Trimmed Text1", each ([Column1] <> "")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.At([Column1],2)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "If underscore is present", each if Text.At([Column1],2)="_" and Text.At([Column1],5)="_"then "True" else "False"), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Value.Is(Value.FromText(Text.At([Column1],0)), type text )), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "If first and second letter are Alpha", each if List.Contains({"A","G"},Text.At([Column1],0)) and List.Contains({"A","U"},Text.At([Column1],1)) then "True" else "False"), #"Renamed Columns" = Table.RenameColumns(#"Added Custom3",{{"Custom", "Return character test"}}), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Custom.2"}), #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Combine cols", each if [If underscore is present] = "True" and [If first and second letter are Alpha] = "True"then true else false) in #"Added Conditional Column"You may pm if you have any questions.
Let me know if you have any questions. (You can combine both columns into one)
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
You can still wield regex in Power Query
let
RE = (regex as text, str as text) =>
let
html = "<script>var regex = " & regex & "; var str = """ & str & """; var res = regex.test(str); document.write(res)</script>",
res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in res,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg+NdwyJD3CNLynKLy1OLSqOTyrNK1aK1UFIwTiJBQU5qcUwgdz8vOzUymKn1ESQnsyilOL45MSS4viU/HSI9nhHR6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
#"Added Custom" = Table.AddColumn(Source, "Test", each RE("/\b[a-z]{2}(_[a-z]{2}){2,}.*/gi", [Column]))
in
#"Added Custom"