How to convert DATE/TIME Column to duration?

[ggarciadynamik4]

Hello Everyone, 

I linked an external database to access and then linked from access to PowerBI.
I am trying to create a dashboard to extract worked hours, but when doing the link described above, the worked hours came as DATE/TIME, not duration, so PowerBI does not sum the hours correctly, I need to convert DATE/TIME to duration but didn't find any solution to make this. 

I already tried to do as bellow 
EDIT QUERY / Transform Collum / Transform Time only  = The time came i.e. 5:00:00. 
Also tried to convert as Duration, but PowerBI just give me a message of error, not allowing to convert it. 

Can someone help me?

[Paola92]

@d_gosbell  it dosn't work if duration if longer than 24 hr. Is there any solution to this case? 

[d_gosbell]

---

@Paola92 wrote:

@d_gosbell  it dosn't work if duration if longer than 24 hr. Is there any solution to this case? 

---

It depends what format your input is in. The original question here was about converting a time column to a duration and times cannot have more than 24 hrs. So you either have a text value or maybe a datetime.

If you have a text value something like "27:35:12" in your data then you can use a function like the following example to convert the format into the following format ddd.hh:mm:ss which is what power query uses for durations longer than 24 hrs

let
    fixDuration = (input as text) =>
        let
            parts = Splitter.SplitTextByDelimiter(":")(input),
            hour = Number.FromText(parts{0}),
            days = Number.RoundDown( hour / 24),
            newHour = Number.ToText( Number.Mod(hour, 24)),
            newValue = if days > 0 
                then Duration.FromText(Number.ToText(days) & "." & newHour & ":" & parts{1} & ":" & parts{2})  
                else Duration.FromText(input)
        in
            newValue,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjK3Mja1MjRSitWJVjLUM7YyNrMyNFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextColumn = _t]),
    #"Added Custom" = Table.AddColumn(Source, "DurationColumn", each fixDuration([TextColumn]))
in
    #"Added Custom"

if your data is already in ddd.hh:mm:ss format you can just use Duration.FromText to convert the type 

[d_gosbell · Accepted solution]

You cannot automatically convert a Time column to a duration since a Time is a single point in time so it has no duration. But in your case it sounds like something like 05:00 should be a duration of 5 hours. So one way to do this is calculate the duration from midnight to the specified time. You can do this by subtracting a time of "00:00" from your current time column (subtracting one time from another returns a duration)

If you click on "Add Column" in the ribbon then choose the "Custom Column" optoin and enter a formula like the following it will return a duration.

[YourTimeColumn] - Time.FromText("00:00:00")

[searl]

worked perfectly - thank you

[Greg_Deckler]

@ggarciadynamik4 Chelsie Eiden's Duration - Microsoft Power BI Community