Forum Discussion

smpa01's avatar
smpa01
Community Champion
7 years ago
Solved

Testing if integer in PBI (M not DAX)

Hello experts,

 

Is it possible for M to test whether my number values are integers?

 

Column1
0.2212
0.45554321
0.6
0.84321896
1
1.2
1.000004
1.76219026
1.8212154
2

Raw Data

Column1Is Integer?
0.2212F
0.45554321F
0.6F
0.84321896F
1T
1.2F
1.000004F
1.76219026F
1.8212154F
2T

 

 Desired Output

Thank you in advance.

  • Hi,

    In the Query Editor, use this M code

    =if Number.Mod([Column1],1)=0 then "T" else "F"

    Hope this helps.

     

4 Replies

  • Hi,

    In the Query Editor, use this M code

    =if Number.Mod([Column1],1)=0 then "T" else "F"

    Hope this helps.

     

  • smpa01's avatar
    smpa01
    Community Champion

    I am not sure if M has a syntax that can check for "is integer". If someone knows about it and please points me to the right direction that would be great.

     

    In the meantime, I thought of something like this.

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYzBDcAwCAN3ybtC2IWUzBJl/zUaBP7Yhy32HiokOM6T0dzdXqJxtkfeYhVWCWG7pqzhm8RS9lLivoZXeffnBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column1 - Copy", type text}}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1 - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", Int64.Type}, {"Column1 - Copy.2", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each if [#"Column1 - Copy.2"]=null then "T" else "F"),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1 - Copy.1", "Column1 - Copy.2"})
    in
        #"Removed Columns"

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion

      smpa01 

       

      How about this as a custom column

       

      =if [Column1]=Int64.From([Column1]) then "T" else "F"
      • smpa01's avatar
        smpa01
        Community Champion

        Zubair_Muhammad  great thanks. I can loose two steps with your solution. Do you know if there is any built-in M syntax that tests for an integer?