Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
5 years ago
Solved

Validate numeric values and 8 digits length

Hello - I need help to create a measure to check if the values in the below table are all numeric and 8 digits then return ok else error. 

 

Table 1

 

Serial No
00809898
00810098
00810298
00810498
00810698
00810898
'00811098
00811298
CGH889
BGD
ISR
"909K
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  gauravnarchal ,

    If you want to use meausre, please refer to what mahoneypat  said and rewrite the formula to look like this:

     

    Measure = 
IF(
    LEN(MAX('Table1'[Serial No])) = 8 
    &&  
    IFERROR(VALUE(MAX('Table1'[Serial No])), 0)>0,"Valid", "Error")

     


    You can also use the m language of Power Query

    Here are the steps you can follow：

    1. Enter the Power query through Transform data and select Add Column – Custom Column

    2. Enter the code

     

    if
List.AllTrue({Text.Length([Serial No]) = 8 ,Value.Is(Value.FromText([Serial No]), type number)})
then
"corrert"
else
"error"

     

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You can use a DAX column expression like this.  Replace Data with your actual table name.

     

    IsValid = IF(LEN(Data[Serial No]) = 8 && IFERROR(VALUE(Data[Serial No]), 0)>0, "Valid", "Error")
     

     

    Pat

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMLC0sLRQitUBcwwNDJA5RsgcE2SOGTIHZkBMqYGBkTlIyBDZGEOYMc7uHhYWlmCmk7sLmPYMDgLTlgaW3kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial No" = _t]),
    Flag = Table.AddColumn(Source, "8 digits", each Text.Length([Serial No])=8 and not (try Number.From([Serial No]))[HasError])
in
    Flag

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  gauravnarchal ,

    If you want to use meausre, please refer to what mahoneypat  said and rewrite the formula to look like this:

     

    Measure = 
IF(
    LEN(MAX('Table1'[Serial No])) = 8 
    &&  
    IFERROR(VALUE(MAX('Table1'[Serial No])), 0)>0,"Valid", "Error")

     


    You can also use the m language of Power Query

    Here are the steps you can follow：

    1. Enter the Power query through Transform data and select Add Column – Custom Column

    2. Enter the code

     

    if
List.AllTrue({Text.Length([Serial No]) = 8 ,Value.Is(Value.FromText([Serial No]), type number)})
then
"corrert"
else
"error"

     

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly