topic Re: Issue when importing data with time greater than 24 hour from Excel in Power BI in DAX Commands and Tips

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

Radhika_Kanaka — Mon, 23 May 2022 18:35:26 GMT

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

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

tamerj1 — Mon, 23 May 2022 18:48:30 GMT

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.

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

Radhika_Kanaka — Mon, 23 May 2022 19:15:14 GMT

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.

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

Radhika_Kanaka — Mon, 23 May 2022 20:17:39 GMT

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

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

tamerj1 — Tue, 24 May 2022 01:34:55 GMT

Ok. Then convert to text

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

tamerj1 — Tue, 24 May 2022 04:01:34 GMT

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

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

Radhika_Kanaka — Tue, 24 May 2022 20:18:24 GMT

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

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]))

Return UNICHAR(10)

& Result

Highly appreciate your help.!!

Thanks,

Radhika

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

tamerj1 — Wed, 25 May 2022 08:19:33 GMT

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

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

tamerj1 — Wed, 25 May 2022 08:19:38 GMT