Forum Discussion

Veternus's avatar
Veternus
Frequent Visitor
2 years ago
Solved

Duration errors

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:...
Need Help
  • Veternus's avatar
    Veternus
    2 years ago

    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's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
2 years ago

Can you post the 'advanced editor' code please?

  • Veternus's avatar
    Veternus
    Frequent Visitor
    2 years ago

    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.