Issue when importing data with time greater than 24 hour from Excel in Power BI

[Radhika_Kanaka]

Hi,

I am trying to import data from Excel into Power BI . I have a column that has time values in it. Power BI has issue when the value exceeds 24 hour format. 

The data in excel is :

I need the same values in Power BI,  but instead below values are dsipalyed.  I tried changing the data type to text and then converting it to duration. but nothing worked out. below is the sample data in Power BI.

Please advice on how to resolve this issue.

Thanks,

Radhika 

[tamerj1 · Accepted solution]

Hi @Radhika_Kanaka 
Please follow these steps

In Excel convert to text. This will automatically convert the value to decimal (Days).

In Power query just convert to decimal data type

In Power BI create a new calculated column

Time Text = 
VAR TimeSeconds = Sheet1[Time] * 86400
VAR NumberOfHours = QUOTIENT ( TimeSeconds, 3600 )
VAR NumberOfMinutes = QUOTIENT ( MOD ( TimeSeconds, 3600 ), 60 )
VAR NumberOfSeconds = MOD ( MOD ( TimeSeconds, 3600 ), 60 )
RETURN
   NumberOfHours & ":" & NumberOfMinutes & ":" & NumberOfSeconds

[Radhika_Kanaka]

Thanks a lot @tamerj1  for the detailed explanation. The client changed the requirement now and added a column with the minutes in Excel .

My DAX formula : this is pretty straight forward. I am still not getting the correct Average resolution time which is 26hrs. Could you please let me know if I missed something here?

NOTE: Both the DAX queries gave the same average resolution time.

Medium_Resolution_Timestamp =

Var ART=

calculate(AVERAGE(Source[Resolved]),filter((source),Source[Priority]="Medium"),DATESINPERIOD(Source[Resolved At].[Date],MAX(Source[Resolved At]),-30,DAY))

Var Result=if(isblank(ART),"No Data Available" & UNICHAR ( 10 ) &"Data displayed is: last 30 days since" & " (" & (today()-30) &")"& ". Priority is: Medium ",FORMAT(ART,"HH:MM" )& UNICHAR ( 10 ) & UNICHAR ( 10 ) & "HH:MM")

Return UNICHAR(10)

& Result

OR

 

Medium_Resolution_Timestamp =

Var ART_30=

calculate(AVERAGE(Source[Resolved]),filter(all(source),Source[Priority]="Medium"),Source[Resolved At]>=max(Source[Resolved At])-30 && Source[Resolved At]<=max(Source[Resolved At]))

Var Result=if(isblank(ART),"No Data Available" & UNICHAR ( 10 ) &"Data displayed is: last 30 days since" & " (" & (today()-30) &")"& ". Priority is: Medium ",FORMAT(ART,"HH:MM" )& UNICHAR ( 10 ) & UNICHAR ( 10 ) & "HH:MM")

Return UNICHAR(10)

& Result

Highly appreciate your help.!!

Thanks,

Radhika 

[tamerj1]

HI @Radhika_Kanaka 
Not sure if the DATESINPERIOD function works properly in your case as you don't have a date table. However, I strongly recommend to use a date table. 
A small modification on your measure

Medium_Resolution_Timestamp =
VAR ART =
    CALCULATE (
        AVERAGE ( Source[Resolved] ),
        FILTER ( ( source ), Source[Priority] = "Medium" ),
        DATESINPERIOD (
            Source[Resolved At].[Date],
            MAX ( Source[Resolved At] ),
            -30,
            DAY
        )
    )
VAR Result =
    IF (
        ISBLANK ( ART ),
        "No Data Available" & UNICHAR ( 10 ) & "Data displayed is: last 30 days since" & " ("
            & ( TODAY () - 30 ) & ")" & ". Priority is: Medium ",
        QUOTIENT ( ART, 60 ) & ":"
            & MOD ( ART, 60 )
            & UNICHAR ( 10 )
            & UNICHAR ( 10 ) & "HH:MM"
    )
RETURN
    UNICHAR ( 10 ) & Result

[tamerj1]

HI @Radhika_Kanaka 
Not sure if the DATESINPERIOD function works properly in your case as you don't have a date table. However, I strongly recommend to use a date table. 
A small modification on your measure

Medium_Resolution_Timestamp =
VAR ART =
    CALCULATE (
        AVERAGE ( Source[Resolved] ),
        FILTER ( ( source ), Source[Priority] = "Medium" ),
        DATESINPERIOD (
            Source[Resolved At].[Date],
            MAX ( Source[Resolved At] ),
            -30,
            DAY
        )
    )
VAR Result =
    IF (
        ISBLANK ( ART ),
        "No Data Available" & UNICHAR ( 10 ) & "Data displayed is: last 30 days since" & " ("
            & ( TODAY () - 30 ) & ")" & ". Priority is: Medium ",
        QUOTIENT ( ART, 60 ) & ":"
            & MOD ( ART, 60 )
            & UNICHAR ( 10 )
            & UNICHAR ( 10 ) & "HH:MM"
    )
RETURN
    UNICHAR ( 10 ) & Result

[Radhika_Kanaka]

I see that the data in Excel is not consistent. few rows have the time format and the rest have date/time format .

For example, if the value is less than 24 hours, it displays just the time. but if it is greater than 24 hours, it displays both date and time .

1/1/1900 9:32:54 AM instead of 33:32:54.

I am not sure this is something that can be fixed in PBI . Please share your inputs on this issue.

Appreciate your time.

Thanks and Regards,

Radhika Lanka

[tamerj1]

Hi @Radhika_Kanaka 

in excel try to convet the column data type into text data type. In power query make sure to delete any automatic type conversion step. 

[Radhika_Kanaka]

Hi @tamerj1 ,

I changed the datatype to text in Excel and deleted the automatic conversion in Power BI by modifying 'Change type' step in Query editor.

[tamerj1]

Ok. Then convert to text