Query gives zero result for group with item values <1

M

mcnicollwj02

New Member
Joined
Nov 6, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Source Data:

Labor Forecast by Person - Query Issue.xlsm
ABCDEFG
1Month End DateCharge CodeResource TypeSourceNameHoursAmount
211/30/2020JOIST01-00001-00060-00000LaborFcstCalvin Coolidge109.95$8,691.93
312/31/2020JOIST01-00001-00060-00000LaborFcstCalvin Coolidge137.44$10,864.91
41/31/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge130.57$10,321.66
52/28/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge121.98$9,642.61
63/31/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge158.06$12,494.65
74/30/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge137.44$10,864.91
85/31/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge137.44$10,864.91
96/30/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge116.82$9,235.17
107/31/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge109.95$8,691.93
118/31/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge151.18$11,951.40
129/30/2021JOIST01-00001-00060-00000LaborFcstCalvin Coolidge144.31$11,408.16
1311/30/2020JOIST01-00001-00060-00000LaborFcstGrover Cleveland109.95$11,796.40
1412/31/2020JOIST01-00001-00060-00000LaborFcstGrover Cleveland137.44$14,745.50
151/31/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland123.70$13,270.95
162/28/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland123.70$13,270.95
173/31/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland146.03$15,667.10
184/30/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland137.44$14,745.50
195/31/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland127.13$13,639.59
206/30/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland144.31$15,482.78
217/31/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland144.31$15,482.78
228/31/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland151.18$16,220.05
239/30/2021JOIST01-00001-00060-00000LaborFcstGrover Cleveland144.31$15,482.78
2411/30/2020JOIST01-00001-00011-00000LaborFcstHerbert Hoover91.05$4,381.75
2512/31/2020JOIST01-00001-00011-00000LaborFcstHerbert Hoover118.54$5,704.55
261/31/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover104.80$5,043.15
272/28/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover130.57$6,283.27
283/31/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover127.13$6,117.92
294/30/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover91.05$4,381.75
305/31/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover111.67$5,373.85
316/30/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover91.05$4,381.75
327/31/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover101.36$4,877.80
338/31/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover125.41$6,035.24
349/30/2021JOIST01-00001-00011-00000LaborFcstHerbert Hoover118.54$5,704.55
3511/30/2020JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
3612/31/2020JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
371/31/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
382/28/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover0.000
393/31/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
404/30/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
415/31/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
426/30/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
437/31/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
448/31/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
459/30/2021JOIST01-00001-00024-00000LaborFcstHerbert Hoover8.59$413.37
4611/30/2020JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
4712/31/2020JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
481/31/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
492/28/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover0.000
503/31/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
514/30/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
525/31/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
536/30/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
547/31/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
558/31/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
569/30/2021JOIST01-00001-00042-00000LaborFcstHerbert Hoover17.18$826.75
5711/30/2020JOIST01-00001-00033-00001LaborFcstRacer X0.30$35.41
5812/31/2020JOIST01-00001-00033-00001LaborFcstRacer X0.35$40.93
591/31/2021JOIST01-00001-00033-00001LaborFcstRacer X0.30$35.41
602/28/2021JOIST01-00001-00033-00001LaborFcstRacer X0.30$35.41
613/31/2021JOIST01-00001-00033-00001LaborFcstRacer X0.34$42.94
624/30/2021JOIST01-00001-00033-00001LaborFcstRacer X0.33$41.44
635/31/2021JOIST01-00001-00033-00001LaborFcstRacer X0.32$37.42
646/30/2021JOIST01-00001-00033-00001LaborFcstRacer X0.35$40.93
657/31/2021JOIST01-00001-00033-00001LaborFcstRacer X0.32$37.42
668/31/2021JOIST01-00001-00033-00001LaborFcstRacer X0.35$41.44
679/30/2021JOIST01-00001-00033-00001LaborFcstRacer X0.33$38.93
Output
 
Upvote 0
Query Output:

Labor Forecast by Person - Query Issue.xlsm
AB
1NameSum Hours
2Calvin Coolidge1,454.00
3Grover Cleveland1,488.00
4Herbert Hoover1,472.00
5Racer X0.00
Summed by Person
 
Upvote 0
Query Settings, Applied Steps:

= Excel.CurrentWorkbook(){[Name="OutputData"]}[Content]

= Table.TransformColumnTypes(Source,{{"Name", type text}, {"Month End Date", type datetime}, {"Charge Code", type text}, {"Resource Type", type text}, {"Source", type text}, {"Amount", type number}, {"Hours", Int64.Type}})

= Table.Group(#"Changed Type", {"Name"}, {{"Sum Hours", each List.Sum([Hours]), type number}})
 
Upvote 0
It's a simple summation of hours by person. The hours entries for Racer X are all less than 1. The total for Racer X is 3.61 hours. The query sum gives a zero.
 
Upvote 0
would be nice to see copied M code from Advanced Editor and posted here between code tags
mcode.png
 
Upvote 0
I hadn't even noticed earlier, but the other totals aren't right either. It's rounding the data to the nearest unit and then summing.
 
Upvote 0
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="OutputData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Month End Date", type datetime}, {"Charge Code", type text}, {"Resource Type", type text}, {"Source", type text}, {"Amount", type number}, {"Hours", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Sum Hours", each List.Sum([Hours]), type number}})
in
    #"Grouped Rows"
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Power Query to Merge and Fill in gaps in data
Replies
7
Views
339
alex78
A
Ramballah
  • Solved
VLookup in Power Query?
2
Replies
16
Views
523
Ramballah
Ramballah
L
  • Question Question
Error with making hour column from datetime column
Replies
0
Views
314
LambChoptheKid
L
I
  • Solved
Multiples operations through Power Query
Replies
6
Views
566
GraH
GraH
RICH937
  • Solved
Power Query: Averaging values when greater than zero
Replies
7
Views
673
RICH937
RICH937

Forum statistics

Threads
1,223,757
Messages
6,174,330
Members
452,555
Latest member
colc007

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top