Formula Help

[dillanpatel]

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. 

[#2]

@dillanpatel 

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.

[#3]

@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. 

[#4]

@dillanpatel 

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.