Jun 27 2020 04:34 AM
Hey guys,
I'm not sure on what formula to use to create values in the columns shown in the excel file attached. I have my heart rate data in which the 'date and time' values vary. I want to create a more structure data set in which the 'date and time' values are within time-periods. For example, any heart rate extracted between date XX/XX/2020, 1:00pm - 2:00pm (then the min, max and median to be shown).
FYI it's to help make visualisations on tableau.
Any help will be greatly appreciated, thank you.
Jun 27 2020 06:43 AM
I did that with Power Query and when started to reply recognized you are on Mac. Thus most probably won't work, however file is attached.
Jun 27 2020 08:06 AM
@Sergei Baklan thanks for the help - as I can't accomplish this on mac, would you mind conducting the exact same query fort his document too?
Really appreciate the help Sergei.
Jun 27 2020 12:10 PM
I added the table generated by Power Query.
In another sheet is the same but generated without data model and Power Query. You may add 5 columns to the source table
Formulas are
Start1:
=INT([@Start])+TIME(FLOOR(HOUR([@Start]),1),0,0)
Finish1:
=[@Strart1]+1/24
Min:
=AGGREGATE(17,6,1/([Strart1]=[@Strart1])*[Heart Rate (count/min)],0)
Max:
=AGGREGATE(17,6,1/([Strart1]=[@Strart1])*[Heart Rate (count/min)],4)
Median:
=AGGREGATE(17,6,1/([Strart1]=[@Strart1])*[Heart Rate (count/min)],2)
After that you may pivot that table on Start1. Or, as variant, remove duplicates.
Please check in attached file.