Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
shaonfx1
Regular Visitor

How to get total hours for individual date

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

 

IMG_6539.jpeg

thanks

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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"

 

11.PNG

 

pls see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

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"

 

11.PNG

 

pls see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DateRuntimeDateRuntimeDateRuntimeDateRuntime
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-241:0104-Oct-24 04-Oct-247:1404-Oct-24 
05-Oct-241:3006-Oct-245:2906-Oct-241:0106-Oct-24 
07-Oct-24 07-Oct-24 07-Oct-24 07-Oct-24 
08-Oct-245:2808-Oct-24 08-Oct-245:2608-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-241:0011-Oct-24 11-Oct-246:0012-Oct-247:00
13-Oct-246:3013-Oct-24 14-Oct-246:3015-Oct-240: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

  ...

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1728877001181.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)

vbofengmsft_0-1728874203438.png

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]))))

vbofengmsft_1-1728874246853.png

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]))))

vbofengmsft_2-1728874311214.png

 

Best Regards,

Bof

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.