Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Count rows based on format

Hi All, I have a column that is a decimal number data type, however when the user uploads a file in the incorrect format it will push other data types into that column.   I'm looking for a solutio...
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

     

    You can try this in Power Query.

    let
    Source = Table.FromColumns({ {1, 1, 1.2, 3, "x"} }, type table [Values = any]),
    AddType = Table.AddColumn(Source, "Data Type", each 
        let v = [Values]
        in if Value.Is(v, Number.Type) then 
            (if Number.Mod(v, 1) = 0 then "Integer" else "Number")
        else "Text", type text)
in
    AddType

     

    1,1,1.2,3 and x are the sample values to test whether they're integer, number (with decimal) or a text. The custom column below is what does the checking.

    let v = [Values]
        in if Value.Is(v, Number.Type) then 
            (if Number.Mod(v, 1) = 0 then "Integer" else "Number")
        else "Text"

     

     

     

danextian's avatar
danextian
Super User
1 year ago

Hi Anonymous 

 

You can try this in Power Query.

let
    Source = Table.FromColumns({ {1, 1, 1.2, 3, "x"} }, type table [Values = any]),
    AddType = Table.AddColumn(Source, "Data Type", each 
        let v = [Values]
        in if Value.Is(v, Number.Type) then 
            (if Number.Mod(v, 1) = 0 then "Integer" else "Number")
        else "Text", type text)
in
    AddType

 

1,1,1.2,3 and x are the sample values to test whether they're integer, number (with decimal) or a text. The custom column below is what does the checking.

let v = [Values]
        in if Value.Is(v, Number.Type) then 
            (if Number.Mod(v, 1) = 0 then "Integer" else "Number")
        else "Text"