Duration errors

[Veternus]

Hello good people of the fabric community! 

I have a problem with durations in Power Bi. 

I have following data set: 

Date	Duration
01-I-2024 01:57:11	27h 14m 23s
01-I-2024 03:50:48	27h 32m 48s
01-I-2024 09:05:04	18h 55m 56s
01-I-2024 09:38:58	16h 14m 33s
01-I-2024 10:20:53	25h 00m 34s

When I load the dataset to Power Query, it is shown in this format: 

Some of the durations are few days long, so the date in the second column can go to 10.1.1900 XX:XX:XX. When I first worked with the data set, I have converted the second column to Duration, extracted total minutes in custom column and worked with it in my measures. During refresh of the data set, the Change Type step started returning errors and I can't get it to work again. 

I've tried searching for an answer on how to deal with this problem, but I haven't found a solution yet. 

Could you please advise? 

[HotChilli]

I'm a bit confused about how this data (example 27h 14m 23s) ever converted to Duration type without a more complex parsing function. It's going to throw an error with any of the basic conversion-to-duration functions.

Is that the error you are getting?

[Veternus · Accepted solution]

I should've explained more clearly, I'm sorry. It's not text data, but a date/time information formated in excel. More realistic view of the data is shown on the picture - in the Power Query View.

Now that I think about it, the first step should be to express the difference between the date in the second column and the date 31.12.1899 00:00:00. That will give a real duration value that I will then be able to work with.

[HotChilli]

Can you post the 'advanced editor' code please?

[Veternus]

This is now returning error: 

let
Source = Excel.Workbook(Web.Contents("........................................................................."), null, true),
#"E-Mail First Response_Sheet" = Source{[Item="E-Mail First Response",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(#"E-Mail First Response_Sheet",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Case User First Response SLA (Account Pased SLA Preset) (AVG)", type duration}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Case User First Response SLA (Account Pased SLA Preset) (AVG)", "Duration"}})
in
#"Renamed Columns"

Unfortunately I don't have the first (working) iteration of the code as I'm working on it. 

However, this happened to three more of my queries, where the repair was quicker as there was duration just in matter of minutes, not days as in this case.