Forum Discussion
Detect number in a text string
I want a true/false statement in Power Query to detect numbers in a text string.
45a- TRUE
ab- FALSE
34- TRUE
aa46- TRUE
Hi Anonymous ，
Text.contails could not work on more then one values, similar error refer:
And change yours to the below:
if Text.Contains([Product], "1") or Text.Contains([Product], "2") or Text.Contains([Product], "3") or Text.Contains([Product], "4") or Text.Contains([Product], "5") or Text.Contains([Product], "6") or Text.Contains([Product], "7") or Text.Contains([Product], "8") or Text.Contains([Product], "9") or Text.Contains([Product], "0") then "True" else "False"Did I answer your question? Mark my post as a solution!
Best RegardsLucien
12 Replies
Actually there is another solution if you want to make it dynamic you can use this kind of code
Let's say the name of this field is [Sample1]45a- TRUE
ab- FALSE
34- TRUE
aa46- TRUE
Power Query code:
if List.AnyTrue(List.Transform({0..9},(NumberColumn) => Text.Contains([Sample1],Number.ToText(NumberColumn)))) = true then "TRUE" else "FALSE"
Please put thumbs up if I help you betterWell... that link got me to this:
Using
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkw0NlGK1QEykszy8sCs3FwIlZdvCGYkJRlZghmWFklJSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [List = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([List], {"0".."9"})), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom] = null then false else true), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom"}) in #"Removed Columns"Does that help?
Hi Anonymous ，
Text.contails could not work on more then one values, similar error refer:
And change yours to the below:
if Text.Contains([Product], "1") or Text.Contains([Product], "2") or Text.Contains([Product], "3") or Text.Contains([Product], "4") or Text.Contains([Product], "5") or Text.Contains([Product], "6") or Text.Contains([Product], "7") or Text.Contains([Product], "8") or Text.Contains([Product], "9") or Text.Contains([Product], "0") then "True" else "False"Did I answer your question? Mark my post as a solution!
Best RegardsLucien
Using the "Contains" option in the conditional column interface seems to work for me:
Attached is the file
Hi PaulDBrown ,
Thank you for your reply, based on what you have provided, each time you use Contains, you also judge only one element and then use a combination of multiple judgments to get the final result.
Best Regards
Lucien
