Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
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
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
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
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.
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.
Ok. Then convert to text
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |