powerbi converting duration time hh:mm:ss into date hh:mm:ss

[fstupot]

hi guys,

please can you help.

i have an excel data source that shows duration in hh:mm:ss when it gets pulled into powerbi it adds date and also changes the time.

i just want it to leave it alone!

how do i stop powerbi converting it, or convert it back?

whenever i try changing the format back to hh:mm it gives me the wrong duration

please help 

Lucy x

[fstupot]

hi all,

the start/end time is not the data from where the moving time is calculated so it doesnt work

i dont understand why powerbi can just display it as it shows in excel.

really annoying x

[Nathaniel_C]

Hi @fstupot ,

I brought the two times into PQ and then let PQ create the duration. 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[fstupot]

Vehicle

Group

Start

End

Total Time

Moving

% Moving

Idling

% Idling

Stopped

% Stopped

Stops

Miles

BF63XUV

Staff_Lichfield OPS

02/11/2020 08:05

25/11/2020 14:31

77:46:09

24:29:53

31.5

22:03:16

28.36

24:30:55

31.52

59

457.6

BF67EUK

Staff_Leek OPS

03/11/2020 07:27

25/11/2020 15:39

111:49:52

32:36:49

29.16

11:49:56

10.58

50:06:31

44.81

107

618.9

BF67EUU

Staff_Lichfield OPS

02/11/2020 11:39

25/11/2020 13:52

86:52:07

24:43:01

28.45

29:13:47

33.65

17:42:52

20.39

69

460.4

BF67EUV

Staff_Gailey OPS

02/11/2020 07:23

25/11/2020 15:00

125:52:20

39:36:51

31.47

08:05:15

6.43

61:00:30

48.47

145

804.7

BF67EUW

Staff_Lichfield OPS

02/11/2020 06:44

25/11/2020 14:03

83:54:54

27:08:11

32.34

06:19:19

7.53

27:27:49

32.73

74

459.2

BF67EUY

Staff_Surface Treatments

02/11/2020 07:12

25/11/2020 14:03

123:06:28

23:28:00

19.06

10:27:26

8.49

84:39:28

68.77

133

480.3

[AllisonKennedy · Accepted solution]

Thanks for the data @fstupot 

i'm not entirely sure where your total time is calculated, so if @Nathaniel_C  's solution helps, great. if not, you can try split column by delimiter to convert your duration columns into numeric columns

paste the below code into advanced editor to see what i mean

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLbhNRDIZfJZp15fpyrl4iAQuQQArhoiqqojIREYVFCQveHtsnNCPSRaWRxmP7+Hzz+7+5mV68KvJ583G6mtbH3X5/+/Zw921/mO+/rt69X1sW+ZrompFxhU0xW4rzY4qSClmqVk1FsXs1KXfNYqEQRD8rilLxsIGU0SSoOZ+a2N7ZD6dcoUzbq+CqLzdvzlzz/P0fkpyRqnL9Dymr+Cgi0mQgPltYpdiXd3YIklM1QoTcnAAVy/iflKBRlHx6oQZ9QbV5jlp2QXAs0WTgtGJvjdEmRBJFGtqkkKurNSavikDxFJm8PM4yQowtIVdBSAuw8xpf7w73858ndmiCyYVgiH4JZ8dij6W7YJnGfoIltq/kOAWShCx20PboIG00UfxBwwR1gfXpWe6yBaULd6Ff1Ey3ZI9XqxoIBRiDeMoOkknmelQI37G7Yqzbmqqnagp3deAF15dHrvXvh/3ubl59eJh3xx/zz+OvC9mIn4YjFrcNu4Ms5HZSswMOczkLe2gaOVIz7/fRXxrUkE0kRLTdTtvtXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Vehicle = _t, Group = _t, Start = _t, End = _t, #"Total Time" = _t, Moving = _t, #"% Moving" = _t, Idling = _t, #"% Idling" = _t, Stopped = _t, #"% Stopped" = _t, Stops = _t, Miles = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"Group", type text}, {"Start", type datetime}, {"End", type datetime}, {"Total Time", type text}, {"Moving", type text}, {"% Moving", type number}, {"Idling", type text}, {"% Idling", type number}, {"Stopped", type text}, {"% Stopped", type number}, {"Stops", Int64.Type}, {"Miles", type number}}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [End] - [Start], type duration),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Time Subtraction",{"Vehicle", "Group", "Start", "End", "Moving", "% Moving", "Idling", "% Idling", "Stopped", "% Stopped", "Stops", "Miles", "Subtraction", "Total Time"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Total Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Total Time.hours", "Total Time.minutes", "Total Time.seconds"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Total Time.hours", Int64.Type}, {"Total Time.minutes", Int64.Type}, {"Total Time.seconds", Int64.Type}}),
#"Inserted Total Minutes" = Table.AddColumn(#"Changed Type1", "Subtraction Total Minutes", each Duration.TotalMinutes([Subtraction]), type number),
#"Added Custom" = Table.AddColumn(#"Inserted Total Minutes", "Total Time.Total minutes", each [Total Time.hours]*60 + [Total Time.minutes]+[Total Time.seconds]/60)
in
#"Added Custom"

[AllisonKennedy]

Clear the formatting in Excel - what is the value actually entered in as? Is it date or text? 

If you can provide some sample data in the actual format we can help better.

The Power BI duration data type is converted to decimal number in the report.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types