Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

15 REPLIES 15
gvrajesh
Frequent Visitor

@Anonymous 

Here is another approach

gvrajesh_0-1614827676415.png

 

gvrajesh
Frequent Visitor

@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"

 

gvrajesh_0-1614825617665.png

 

Anonymous
Not applicable

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! 

Jimmy801
Community Champion
Community Champion

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

Anonymous
Not applicable

Hi @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 @Vera_33  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
Not applicable

Hi @Jimmy801 , how do I view in advanced editor? you solution worked on mine.thanks! 

I just want to view in code uisng advanced editor. Sorry newbie question.

Hello @Anonymous 

 

I saw you marked my post as solution. But you have still some questions or is now everything fine`?

 

BR

 

Jimmy

Anonymous
Not applicable

Hi @Jimmy801 , everythin is fine now. Thanks found where is my advanced editor as well!

Vera_33
Resident Rockstar
Resident Rockstar

Hi @Anonymous 

 

Can you provide some sample data? Does your column have number or number+letter only? Simple example like this?

 

Vera_33_0-1614753162700.png

Table.AddColumn(#"Changed Type", "Custom", each 
[a=try Number.From([Column1]) otherwise "Alpha Numeric",
b=if a =[Column1] then "Numeric" else a ][b])

 

Anonymous
Not applicable

Hi @Vera_33 ,

 

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

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?

 

Vera_33_0-1614821978101.png

 

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.

Anonymous
Not applicable

Hello @Vera_33 , 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! 🙂

@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"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors