Forum Discussion
Solved
Help needed (Urgent)!
Hello everyone,
I need help with follwing,
Asset |AssetNum | startdatetime | finishdatetime | comment | hours
1410 | LD03 - 3005 | 2023-07-28 01:59:00.000 | 2023-07-28 10:59:00.000 | Needs Rebuild | 10
249 | LD04 | 2023-07-10 17:00:00.000 | 2023-07-15 00:00:00.000 |Transmission Failure | 103
249 | LD04 | 2023-07-10 17:00:00.000 | 2023-07-15 00:00:00.000 |Transmission Failure | 103
the last column displays the total hours between start time to finish time. What I want it to do is to display the time for each day between start time and finish time so when I create a bar graph it should have following (for asset 249 for instance),
7 hours on 10/07/2023
24 hours on 11/07/2023
24 hours on 12/07/2023
and so on until 15/07/2023.
Is this achieveable on power bi?
Thanks in advance
Hi prajwalpanji
You can try the following code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxBCsAgDAS/EnLWshsVq+ce+wPx/99oUCjektlhxlBmQoPK+yBJlAQUfw2WImq0W8BeWseSDkxsfAEQ35rOMNRy89tb+YgQwupq3+ZfYZEFjwqRdM4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Asset = _t, AssetNum = _t, startdatetime = _t, finishdatetime = _t, hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset", Int64.Type}, {"AssetNum", type text}, {"startdatetime", type datetime}, {"finishdatetime", type datetime}, {"hours", Int64.Type}}), #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([startdatetime]), type date), #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "startDate"}}), #"Inserted Date1" = Table.AddColumn(#"Renamed Columns", "Date", each DateTime.Date([finishdatetime]), type date), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Date1",{{"Date", "engDate"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each List.Dates([startDate], Duration.Days( [engDate]-[startDate])+1,#duration(1, 0, 0, 0) )), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[startDate] and [Custom]=[engDate] then [finishdatetime]-[startdatetime] else if [Custom]=[startDate] then ([Custom]&#time(24,0,0))-[startdatetime] else if [Custom]=[engDate] then [finishdatetime]-([Custom]&#time(0,0,0)) else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Duration.Days([Custom.1])>=1 or [Custom.1]=null then 24 else Duration.Hours([Custom.1])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"startDate", "engDate", "Custom.1"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.2", "Hour_difference"}}) in #"Renamed Columns2"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi prajwalpanji
You can put the following code in Advanced Editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYyxDcAgDARXsVzj6G2DCNQpswFi/zViUSSkPN3pxmDNCk5M9wUnIQdKoMFcUMVOgvbSOnAAK9yU4lMULk4zDbbcAmKYt5OCtEa75e9KCy3xWznP+QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Asset = _t, AssetNum = _t, startdatetime = _t, finishdatetime = _t, hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset", Int64.Type}, {"AssetNum", type text}, {"startdatetime", type datetime}, {"finishdatetime", type datetime}, {"hours", Int64.Type}}), #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([startdatetime]), type date), #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "startDate"}}), #"Inserted Date1" = Table.AddColumn(#"Renamed Columns", "Date", each DateTime.Date([finishdatetime]), type date), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Date1",{{"Date", "engDate"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each List.Dates([startDate], Duration.Days( [engDate]-[startDate])+1,#duration(1, 0, 0, 0) )), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[startDate] and [Custom]=[engDate] then [hours]else if [Custom]=[startDate] then Duration.Hours([Custom]&#time(24,0,0)-[startdatetime]) else if [Custom]=[engDate] then Duration.Hours([finishdatetime]-([Custom]&#time(0,0,0))) else 24), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"startDate", "engDate"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Hour_diff"}}) in #"Renamed Columns2"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yolo Zhu, thanks a lot. It does work. However, when the start time is 12:00 AM, it displays 0 hours instead of 24 hours. Please see the attached image.
Hi prajwalpanji
You can try the following code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxBCsAgDAS/EnLWshsVq+ce+wPx/99oUCjektlhxlBmQoPK+yBJlAQUfw2WImq0W8BeWseSDkxsfAEQ35rOMNRy89tb+YgQwupq3+ZfYZEFjwqRdM4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Asset = _t, AssetNum = _t, startdatetime = _t, finishdatetime = _t, hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset", Int64.Type}, {"AssetNum", type text}, {"startdatetime", type datetime}, {"finishdatetime", type datetime}, {"hours", Int64.Type}}), #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([startdatetime]), type date), #"Renamed Columns" = Table.RenameColumns(#"Inserted Date",{{"Date", "startDate"}}), #"Inserted Date1" = Table.AddColumn(#"Renamed Columns", "Date", each DateTime.Date([finishdatetime]), type date), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Date1",{{"Date", "engDate"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Custom", each List.Dates([startDate], Duration.Days( [engDate]-[startDate])+1,#duration(1, 0, 0, 0) )), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom]=[startDate] and [Custom]=[engDate] then [finishdatetime]-[startdatetime] else if [Custom]=[startDate] then ([Custom]&#time(24,0,0))-[startdatetime] else if [Custom]=[engDate] then [finishdatetime]-([Custom]&#time(0,0,0)) else null), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Duration.Days([Custom.1])>=1 or [Custom.1]=null then 24 else Duration.Hours([Custom.1])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"startDate", "engDate", "Custom.1"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.2", "Hour_difference"}}) in #"Renamed Columns2"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
