Forum Discussion
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,
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 AddType1,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"
4 Replies
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,
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!
😁😁
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 AddType1,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"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.
