How to get total hours for individual date

[shaonfx1]

Hello, i am not able solve the issue. I need to get total hours for individual date using function.

 

thanks

[ryan_mayu · Accepted solution]

you can try this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZJBDoQwCEWvYrrWBFqstZeYA5iu5grePxYyEyldsXDxfx6PpHhdAeL2+d5bpLCGpX++3NZuSIZwZTHQ22AFZIjMkMpHRZoQ8ezak4Ch/FZ7jaep/tvypDrMflcWQxk2F4aKGRqRPCHiOc2QK7MBYSR8WQw4PBk/rK4Wk/MPifpmvRJV0pzcCM0PgjQj6rLAVWsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Runtime = _t, Date.1 = _t, Runtime.1 = _t, Date.2 = _t, Runtime.2 = _t, Date.3 = _t, Runtime.3 = _t]),
Custom1 = Table.ToColumns(Source),
Custom2 = List.Positions(Custom1),
Evenrow = List.Select(Custom2,Number.IsEven),
Oddrow = List.Select(Custom2,Number.IsOdd),
combine = List.Zip({Evenrow,Oddrow}),
getdata = List.Transform(combine,each Table.FromColumns({Custom1{_{0}},Custom1{_{1}}},{"name","value"})),
Custom3 = Table.Combine(getdata),
#"Changed Type" = Table.TransformColumnTypes(Custom3,{{"value", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.From([value])*60*60*24),
#"Promoted Headers" = Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"02-Oct-24"}, {{"total", each List.Sum([Column3]), type nullable number}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.From(Number.IntegerDivide([total],3600)) & ":"& Text.From(Number.Round (Number.Mod([total],3600)/60))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"total"})
in
#"Removed Columns"

pls see the attachment below

[ryan_mayu]

pls paste the sample data here (not the scrrenshot). What's the expected output? Please aslo elaborate the calculation lgo

[shaonfx1]

Date	Runtime	Date	Runtime	Date	Runtime	Date	Runtime
02-Oct-24		02-Oct-24		02-Oct-24		02-Oct-24
03-Oct-24		03-Oct-24		03-Oct-24		03-Oct-24
04-Oct-24	1:01	04-Oct-24		04-Oct-24	7:14	04-Oct-24
05-Oct-24	1:30	06-Oct-24	5:29	06-Oct-24	1:01	06-Oct-24
07-Oct-24		07-Oct-24		07-Oct-24		07-Oct-24
08-Oct-24	5:28	08-Oct-24		08-Oct-24	5:26	08-Oct-24
09-Oct-24		09-Oct-24		09-Oct-24		09-Oct-24
10-Oct-24		10-Oct-24		10-Oct-24		10-Oct-24
11-Oct-24	1:00	11-Oct-24		11-Oct-24	6:00	12-Oct-24	7:00
13-Oct-24	6:30	13-Oct-24		14-Oct-24	6:30	15-Oct-24	0:30

output option 1

04-Oct-24	8:15
05-Oct-24	1:30
06-Oct-24	6:30
08-Oct-24	10:54

output option 2

Oct-24	54:39
Nov-24	...
Dec-24	...
Jan-25	...

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[v-bofeng-msft]

Hi @shaonfx1 ,

@ryan_mayu  thanks for your concern about this case. I tried to create a sample data myself based on the @shaonfx1 's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

1\I assume there is a table (Sheet1)

2\Create a cauclate table for option1

option 1 = SUMMARIZE(Sheet1,[Date],"RunTime",If(ISBLANK(Max(Sheet1[Runtime])),BLANK(),ROUNDDOWN(Value(sum(Sheet1[Runtime]))*24,0)&":"& MINUTE(sum(Sheet1[Runtime]))))

3\Create a caculate table for option 2

option 2 = SUMMARIZE(Sheet1,[Year],[Month],"RunTime",If(ISBLANK(Max(Sheet1[Runtime])),BLANK(),ROUNDDOWN(Value(sum(Sheet1[Runtime]))*24,0)&":"& MINUTE(sum(Sheet1[Runtime]))))

Best Regards,

Bof