Query gives zero result for group with item values <1

[mcnicollwj02]

I have a simple query of a table containing names, month end dates and labor hours. The query is simply a sum of hours by name. For one name having entries for hours that are all less than 1, the query gives a sum of zero. If I change a value in the table to 1, the query gives a result of 1. If I put in any number >1, the query will pick it up. But when all values are <1, the query results in zero. All other data seems to be summing as expected.

Why would the query give a result of zero and how do I get it to give the correct sum?

 

[sandy666]

anyway with fast test without rounding

Name	Sum
Calvin Coolidge	1455.146
Grover Cleveland	1489.506
Herbert Hoover	1468.89
Racer X	3.609784946

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Sum", each List.Sum([Hours]), type number}})
in
    Group

then you can round it

 

[sandy666]

with round

Name	Sum
Calvin Coolidge	1455.15
Grover Cleveland	1489.51
Herbert Hoover	1468.89
Racer X	3.61

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Name"}, {{"Sum", each List.Sum([Hours]), type number}}),
    Round = Table.TransformColumns(Group,{{"Sum", each Number.Round(_, 2), type number}})
in
    Round

IMHO, you should round AFTER sum not BEFORE

 

[mcnicollwj02]

Got it, thanks. I'm wondering how that integer type got in there. I didn't do anything that was meant to change the data type.

 

[sandy666]

Table.TransformColumnTypes is added automatically (you can turn it off as I did) and maybe not recognised your data properly

 

[mcnicollwj02]

How do you turn that off?

 

[sandy666]

unfortunately with another / new workbook it will be turned on
easier and faster is just remove this step

 

[mcnicollwj02]

I am running Excel 365. Query Options are nowhere to be found.

 

[sandy666]

Advanced Editor - File - Options & settings - Query Options (or similar)

 

[mcnicollwj02]

Did you mention Advanced Editor by mistake or is there an Advanced Editor somewhere other than in the Power Query Editor?

File - Options has no Query Options.

 

[sandy666]

sure, it should be Power Query Editor, my mistake

Recommended Power Query Settings

One should never underestimate settings in Power Query. So let's have a look on few recommendations which can save you lots of pain in future. This articles covers only the most relevant settings (not all).

www.excelinppc.com

or use a "normal" Excel not a 365