Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Found a solution. I reckon I was not asking my question correctly.
Refer to Previous Row for Subgroups in Power Query
Found a solution. I reckon I was not asking my question correctly.
Refer to Previous Row for Subgroups in Power Query
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
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
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
It's my code. Let me go fire up the PC, this might not be one to do in my head...
--Nate