Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
Anonymous
Not applicable

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 solution that will allow me to count where a row in the column is not numerical or just allow me to count the number of rows that are not the same data type as the rest, although I'm not sure that's possible.

 

Thanks in advance,

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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_0-1741011117346.png

 

 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

4 REPLIES 4
v-kathullac
Community Support
Community Support

Hi @Anonymous ,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.


Regards,

Chaithanya.

danextian
Super User
Super User

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_0-1741011117346.png

 

 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
freginier
Super User
Super User

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!

😁😁

 

DataNinja777
Super User
Super User

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,

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.