Custom Colum using an if statement to compare dates in a grouped table and sum values

[RappJ]

Good day all,

for each row in the main table I need to sum the hours colum in the nested group under the Runs and tools column. Any help is appreciated.

I belive I need an if statement in a custom column.

custom column = if(table.starttime >= Min Created on && table.startime <= Max Created On, sum(hours)

I know this code is not anywhere near correct i am just trying to explain what i am after.

[RappJ · Accepted solution]

Found a solution. I reckon I was not asking my question correctly.

Refer to Previous Row for Subgroups in Power Query

https://www.youtube.com/watch?v=IGF2-qfzDQs 

[watkinnc]

Oh wait, what am I thinking?! Try this:

= Table.TransformColumn(PriorStepOrTableName, "RunsAndTools"  each Table.SelectRows(_, each [StartTime] >= [Min Created On] and [StartTime] <=[Max Created Time]))

Now you can click the Expand icon, choose "Aggregate", then Sum for the hours column. That should work. If not, then I really need to stop doing these with thoughts alone!

--Nate

[RappJ]

Nate,
Thanks again, however I am unable to make this work either. I have included a screen cap of the advanced editor. I appreciate your assitance. ANy idea what I have done wrong?

#"Added Custom" = Table.TransformRows(#"Reordered Columns", "RunsAndTools" each Table.SelectRows(_, each [StartTime] >= [Min Created On] and [StartTime] <=[Max Created On]),

Regards,
Jason

[watkinnc]

Try this:

= Table.AddColumn(PriorStepOrTableName, "TotalHours", each if [RunsAndTools][StartTime] >= [Min Created On] and [RunsAndTools][StartTime] <=[Max Created Time] then List.Sum([RunsAndTools][Hours]) else null)

See if that gives you what you need.

--Nate

[RappJ]

Nate,
Thank you for the reply. I am wondering if this error is due to user error, or did I impliment the code correctly and Power Query just does not like it. 

Expression.Error: We cannot apply operator >= to types List and Date.
Details:
Operator=>=
Left=[List]
Right=3/14/2018

[watkinnc]

It's my code. Let me go fire up the PC, this might not be one to do in my head...

--Nate