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"

     

     

     

DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi Anonymous ,

 

In Power BI, column data types are enforced, so mixed data types usually result in errors during data transformation. The best way to count non-numeric values is in Power Query before loading the data. Use a custom column with try Number.From([YourColumn]) otherwise "Error" to flag invalid values. Then, filter or sum the occurrences of "Error" to count them. If Power Query coerces numbers into text instead of generating errors, use if Value.Is(Number.FromText([YourColumn]), type number) then 0 else 1 to detect non-numeric values. This ensures proper validation before the data reaches Power BI.

 

Best regards,