Forum Discussion
Mcode: Custom Column- Evaluate IF Text Column has Number Value
Hello Anonymous
you can add a custom column with this formula (you have to replace "Column1" with your real column name)
if List.AllTrue(List.Transform(Text.ToList([Column1]), each try Value.Is(Number.From(_), type number) otherwise false)) then "numeric" else "alpha numeric"Here the complete code example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitWJVjI2BlNRUWDKACIIlXN2hshBeJaWECUGSrGxAA==", 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}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if List.AllTrue(List.Transform(Text.ToList([Column1]), each try Value.Is(Number.From(_), type number) otherwise false)) then "numeric" else "alpha numeric") in #"Added Custom"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi Anonymous
Can you provide some sample data? Does your column have number or number+letter only? Simple example like this?
Table.AddColumn(#"Changed Type", "Custom", each
[a=try Number.From([Column1]) otherwise "Alpha Numeric",
b=if a =[Column1] then "Numeric" else a ][b])
Hi Anonymous ,
Thanks for the response I have column wiht below sample. IF the column purely contains two digit number results should be "Number" Else all is "Alpha Numeric" hence AA and A1 will fall on this category.
00
A1
33
ZZ
01
A1
CC
Z1
99
Anonymousyou can do it in this way
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBQitWJVnJ0BFPGxmCqqgoiaKgUGwsA", 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}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try Number.From([Column1]) otherwise -0.1), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]<>-0.1 then "number" else "alphanumeric") in #"Added Custom1"Hello smpa01 ,
I tried your approach and I am getting Token EoF expected error on below formula.
I highlighted the closing parentheses in red with the error. Did I missed something in my syntax?
let
Source = Table.FromRows(#"Table Master Valid"), let_t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data Type" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{#"Data Type" , type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try Number.From([#"Data Type" ]) otherwise -0.1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]<>-0.1 then "number" else "alphanumeric")
in
#"Added Custom1"
Hi Anonymous
So you don't need to verify if there are only 2 digits? It can only be 2 digits or sample like AA and A1? Do you have more than 2 digits or letters you need to take care?
And your question towards Jimmy801 solution, you need to paste all the code in Advanced Editor with a blank query, then you can see it all in one query.
Hello Anonymous , Yes only two digits. Sorry I am really new to Mcode, when you say Advanced Editor you mean Add Column > Custom Column right?
BTW Vera, not sure why my results is always a table when I try your code.
Thank you for you responses! 🙂
