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"

     

     

     

freginier's avatar
freginier
Solution Sage
1 year ago

Hey there! 

 

You shoudl try Power Query!

-Go to Power Query Editor (Transform Data).
- Add a Custom Column:
- Click Add Column > Custom Column
Use this formula: if (try Number.From([YourColumn]) is null) then "Invalid" else "Valid"

- This will tag rows as "Invalid" if they contain non-numeric values.
- Filter or Count Invalid Values:
- Click on the new column’s filter, select only "Invalid", then count rows.

 

If you're not comfortable with Power Query you could also try DAX: 

Count_Non_Numeric =
COUNTROWS(
FILTER(
TableName,
ISBLANK( VALUE( TableName[YourColumn] ) )
)
)

 

VALUE() converts text to numbers and returns blank for non-numeric values.
ISBLANK() ensures only non-numeric values are counted.

 

Hope this helps!

😁😁