Forum Discussion
Mcode: Custom Column- Evaluate IF Text Column has Number Value
Hello,
I have a Text Column that has values 00 to 99 and AA to ZZ, how do I eveluate in M to check IF the column has number value?
IF it column is between the number range the Custom Column will have a "Number" value Else it should be "Alpha Numeric".
An inputs and guidance is appreciated.
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
15 Replies
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"
Anonymous
Here is another approach
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
JimmyHi Jimmy801 ,
I cannot understnad this part of the code:
FromText("i45WcjRUitWJVjI2BlNRUWDKACIIlXN2hshBeJaWECUGSrGxAA==",)
Should I be updatin this?
Hello Anonymous
you can copy paste my first code as formula of a new custom column, adapting the column reference. Or you copy paste the second code into your advanced editor to see a whole example, just as Anonymous was writing.
Hope this helps
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
Anonymous
Create custome column like below
if Text.Contains("0123456789",Text.Start([ID],1)) and Text.Contains("0123456789",Text.End([ID],1)) then "Number" else "Alpha"
Thanks for you inputs gvrajesh for both solution I always get Token Comma expected error, even if I written all the codes from your solution. Thanks for your inputs!
