Group By Not Working Because of Nulls (But there are no Nulls?)

[cmaloyb]

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?  

[Anonymous · Accepted solution]

@cmaloyb 

I'm not really sure, but I fear that the quality report refers only to the first 1000 lines

[cmaloyb]

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! 

[v-eachen-msft]

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.

[Anonymous]

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.

[Anonymous]

the value null,in this case,   raises from sum of two values one of wich in null

[az38]

@cmaloyb 

it's not about GROUP BY

it's about convert one of your fields to True/False data type.

Check advanced editor and find (and remove if you need)

{"ColumnName", type logical}

[cmaloyb]

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. 

Column Quality

[az38]

@cmaloyb 

please, provide all code from advanced editor. before you can remove all sensitive data.