Forum Discussion

gauravnarchal's avatar
gauravnarchal
Icon for Post Prodigy rankPost 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 0081...
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

     

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity 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