Forum Discussion
Custom Colum using an if statement to compare dates in a grouped table and sum values
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.
Found a solution. I reckon I was not asking my question correctly.
Refer to Previous Row for Subgroups in Power Query
6 Replies
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
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
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
Found a solution. I reckon I was not asking my question correctly.
Refer to Previous Row for Subgroups in Power Query
