Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
When I am going a group by function, I am grouping three columns of the same data type into a new summed column.
I am getting the error:
Expression.Error: We cannot convert the value null to type Logical.
Details:
Value=
Type=[Type]
I have replaced all values that were considered null to something else, however this error still returns. I turned on the Column Quality in the step previous to the Group By and there is 100% Valid in each of the fields whereas Error and Empty are all at 0%.
Any ideas on why the query is telling me there are nulls when I can not find any evidence of them?
Solved! Go to Solution.
I'm not really sure, but I fear that the quality report refers only to the first 1000 lines
I'm not really sure, but I fear that the quality report refers only to the first 1000 lines
Hi @Anonymous ,
That is exactly what I found. After using your given recommendations (which I tried previously using my own personal M), I realized that I may just be looking at the first 1000 rows.
After further investigation, I finally found an ID# that was null which, as explained in your previous post, returned an error in the applied step where I had built a conditional column.
Thank you Rocco and to all who gave input!
Hi @cmaloyb ,
You could use the following codes to check if there is null value.
= Table.AddColumn(#"Changed Type", "Custom", each if [Column1]= null or [Column2] = null or [Column3]= null then "null" else "positive")
Or you could use 0 or "null" to replace null values.
this type of error can raise if an expression following "if" results in null.
you could check if you have expressions evaluated by "if" and if these expressions, in some cases, become null.
I would try to apply the expressions only to the first rows of the table (removing the rows after the first, say, 3) to see if the problem appears for some particular value of the following rows.
the value null,in this case, raises from sum of two values one of wich in null
Hi @az38 ,
I should have said that in order to continute building my query, I need to do a group by step.
I have gone through the advanced editor and have not found anywhere where there is:
{"ColumnName", type logical}
The following is the step I need to move forward with but is holding me back because of nulls that I am not seeing anywhere.
#"Grouped Rows" = Table.Group(#"Replaced Value", {"JobID#", "OfficeAbbrv", "ReportMonth"}, {"ReportSum", each List.Sum([ReportCount]), type number})
JobID# - Unique identifier for job position. Type text.
OfficeAbbrv - Location of employees office. Type text.
ReportMonth - Month YYYY. Type Text
ReportCount - 1 or 0 depending on if the employee submitted the report for given ReportMonth. Type Int64.Type
Supervisor - Name of Supervisor (Not used in Group By step).
This is the column quality in the previous step before I want to do the Group By.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.