GroupBy return "We cannot convert the value null to type Logical", can't find a null value ! ?

[Watever]

I have what is seems like a simple problem but can't figure it out. It's not powerbi but excel, but since it's powerquery and members here are better for this than the excel forum, I asked here.

I finish a table by groupby, unfortunately, it always return an expression error : We cannot convert the value null to type Logical

I verified my data, I removed any errors, I filtered any "empty" value previously in all the columns, etc... and I still get that error.

I can't figure out what to do anymore. Anyone have a tips on this ?

Some of my code

 #"Added Pal Select" = Table.AddColumn(#"Changed Ratio Percentage", "Pal_Select", each if [#"Ratio Cs/Pal"] >= [#"Pal. Rnd. PCT."] then [Qte Pallet]+1 else [Qte Pallet], Int64.Type),
    #"Added Cs Pick" = Table.AddColumn(#"Added Pal Select", "Cs Pick", each if [Pal_Select] = [Qte Pallet] then [Qte Caisse] else 0),
    
    #"Filtered Rows1" = Table.SelectRows(#"Added Cs Pick", each ([Cs Pick] <> 0 and [Cs Pick] <> null and [Cs Pick] <> "")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Article", "Description", "Route", "Cs Pick"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Article", Int64.Type}, {"Description", type text}, {"Route", Int64.Type}, {"Cs Pick", Int64.Type}}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Article", "Description", "Route", "Cs Pick"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Errors1", each [Article] <> null and [Article] <> ""),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each [Route] <> null and [Route] <> ""),
    #"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each [Cs Pick] <> null and [Cs Pick] <> ""),
    #"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each [Description] <> null and [Description] <> ""),
    #"Grouped Rows" = Table.Group(#"Filtered Rows5", {"Article"}, {{"Total", each List.Sum([Cs Pick]), type number}})
in
    #"Grouped Rows"

[PwerQueryKees]

Are there any nulls in you [Article] column? If there are, change them to a zero length string "" or filter the rows to exclude them.

[Cookistador]

Hi @Watever 

I had something similar a few weeks ago, this workaround should work 

Replace

= Table.Group(#"Removed Other Columns1", {"Article"}, {{"Total", each List.Sum([Cs Pick]), type number}})

by

= Table.Group(#"Removed Other Columns1", {"Article"}, {{"Total", each List.Sum([Cs Pick]) ?? 0, type number}})

the ?? is null coalescing operator, if it returns null, it will replace it by 0

if you need more information about this operator, you can find some explainations under the followig link

https://gorilla.bi/power-query/coalesce/

Keep me infom if it is not fixed your issue 🙂