Importing Duration Data from Excel - Calculating Average Duration

[bvbull200]

I am using Excel to calculate the number of hours between two instances.  This data is all in Excel.  It takes the start date from [Request Creation Date], the start time from [Request Creation Time] and figures the number of working hours between that and the date from [First Action Date] and the end time from [First Action Time].  In case it helps provide any clarity, this is the formula to execute it:

=(NETWORKDAYS(TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"),TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss"))-1)*("17:00"-"8:00")+IF(NETWORKDAYS(TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss"),TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss")),MEDIAN(MOD(TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss"),1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"),TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"))*MOD(TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"),1),"17:00","8:00")

That data is formatted as: [h]:mm:ss

When the table containing this information is imported in to Power BI, the query editor displays the information as a time of the day.  For instance, my Excel table has the result of 3:49:06 and takes it as 3 hours, 49 minutes, and 6 seconds.  That same cell of data in Power BI is 3:49:06 PM as in a time in the afternoon.  Worse yet, in Excel, I have the value 0:22:32, which is 22 minutes and 32 seconds.  Power BI forces it to be 12:22:32 AM.

In Excel, I can calculate the average between two times.  If I were to average 0:30:00 and 4:30:00, I would get 2:30:00.  Because Power BI forces this to be a time of the day, there is no option for summing or averaging the values.  

I can get this data to "appear" to be formatted the way I want in a table on the desktop, but since the info is still really just a time of the day instead of a duration, I can't do any useful calculations/summarizations.

I have tried changing the format to "Duration" in the Power Query Editor, but that just throws up an error.

Are there any suggestions for how to get the data to stay in the [h]:mm:ss format that it exists as in Excel without Power BI adding unnecessary (and invalid) formatting to it?  

Thank you for any help on the matter.

[parry2k]

@bvbull200 hope this post helps.

[bvbull200]

@parry2k 

Thank you for offering a potential solution.  I have worked through that thread, but I don't think that my solution is in there.  It appears that the OP suggestion, and many suggestions following, is reliant on the data being the total number of a single unit of time - for instance, the total number of seconds or the total number of minutes.  All manipulation is done with that data, then reconverted back.

That essentially means even more calculation in Excel prior to loading to Power BI. 

Does Power BI offer *any* means of interpreting 0:22:15 as 22 minutes and 15 seconds or is it forever doomed to be interpreted as 12:22:15 AM?

[parry2k · Accepted solution]

@bvbull200 you can add custom column to get hours and minutes from your time and that will make calculations easy. Here followng code in blank query and you can check on extracting hours and minutes. Hope it helps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjKyMjRVitUBcwwNYBxDKyDXwEApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Minutes", each (Time.Minute([Column1])), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hours", each (Time.Hour([Column1])), Int64.Type)
in
    #"Added Custom1"