The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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,
Solved! Go to Solution.
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"
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.
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"
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 ,
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,
User | Count |
---|---|
58 | |
56 | |
53 | |
49 | |
32 |
User | Count |
---|---|
173 | |
89 | |
70 | |
46 | |
45 |