Help needed (Urgent)!

[prajwalpanji]

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

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

[Anonymous]

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.

[prajwalpanji]

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. 

[Anonymous · Accepted solution]

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.

[prajwalpanji]

Cheers @Anonymous.