cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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?  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

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

@cmaloyb 

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

View solution in original post

8 REPLIES 8
Highlighted
Super User VI
Super User VI

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

@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}

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Helper I
Helper I

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

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 QualityColumn Quality

Highlighted
Super User VI
Super User VI

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

@cmaloyb 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Highlighted
Memorable Member
Memorable Member

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

this type of error can raise if an expression following "if" results in null.

 

image.png

 

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.

Highlighted
Memorable Member
Memorable Member

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

image.png

 

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

Highlighted
Community Support
Community Support

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

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Memorable Member
Memorable Member

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

@cmaloyb 

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

View solution in original post

Highlighted
Helper I
Helper I

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

Hi @Rocco_sprmnt21 ,

 

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! 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors