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
Hello, i am not able solve the issue. I need to get total hours for individual date using function.
thanks
Solved! Go to 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
Proud to be a 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"
pls see the attachment below
Proud to be a Super User!
pls paste the sample data here (not the scrrenshot). What's the expected output? Please aslo elaborate the calculation lgo
Proud to be a Super User!
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 | ... |
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
83 | |
76 | |
64 |
User | Count |
---|---|
142 | |
112 | |
98 | |
98 | |
94 |