Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
Am facing problem in time. can u please anyone help me or guide me .
Prob :
My source is Excel.
Duration is filed name
Field value like "15:20:00" like "HH:MM:SS"
when am getting the excel in our power bi, the column is automatically converted as
but i need exact value like same as excel. "31-12-1899 03:20:0"
thanks & am waiting for your reply.
Solved! Go to Solution.
@Baskar in the query editor, convert the duration column to a decimal data type. Then after you load the query, go to the table editor and add a column:
New Duration = FORMAT(TableName[Duration], "HH:mm:ss")
Obviously you could also rename the original duration column something like OldDuration and name the new one Duration so you can still use a column with the same name.
Proud to be a Super User!
@Baskar in the query editor, convert the duration column to a decimal data type. Then after you load the query, go to the table editor and add a column:
New Duration = FORMAT(TableName[Duration], "HH:mm:ss")
Obviously you could also rename the original duration column something like OldDuration and name the new one Duration so you can still use a column with the same name.
Proud to be a Super User!
This solution dose not work the Excell format [H]:mm:ss returns total hours so if decimall is 2.241967593 it returns 53:48:26
wich is the whole number
the format above only returns the time element to the right of the decimal point
I need to show total Duration in hours
Any ideas
Thanks Garry
Hi @Garry,
Have you got a solution ?
I also had the same requirement. I have to aggregate time for the day, week, month and for any selected time range by slicers.
There is no straight forward solution in Power BI yet.
I broke down calculation to Hours, minutes and seconds and then represent in format to be able to serve my purpose.
MEASURES -
CALLTIMES = SUM(DURATIONINSECONDS)
HRS = TRUNC([CALLTIMES]/3600)
MINS = TRUNC((([CALLTIMES]/3600)-[HRS])*60,0)
SECS = TRUNC((((([CALLTIMES]/3600)-TRUNC([CALLTIMES]/3600))*60)-[MINS])*60)
TIMING = IF(LEN([HRS])=1,"0"&[HRS],[HRS])&":"&IF(LEN([MINS])=1,"0"&[MINS],[MINS])&":"&IF(LEN([SECS])=1,"0"&[SECS],[SECS])
This solution is elegant.
But what if you need to apply rounding, how to proceed ????
Hi @Anonymous,
The measured I have mentioned above should round off the numers for you. Else, please share an example so I can tell what can be done.
I use if to solve this, i create others measures where i roud the number with criterea i need, not beatiful , but usefull
exmples below:
ConsumoMinuto = if ([CONSUMO_SECS]>=30;[ConsumoM]+1;[ConsumoM])
I am work to round a hour now.
Thanks
Oh I see.
Hi @Anonymous,
The measured I have mentioned above should round off the numers for you. Else, please share an example so I can tell what can be done.
This solution is elegant
But what if you need to apply rounding, how to proceed ????
I had the same issue, when i made the change in query editor (to make the column decimal format) it had values i applied thoose changes, but when i when to add a new column in the table area that decimal column only had a value of zero in all that column?
I have the same problem but when i apply the decimal changes in the query editor and then go to add the new column, the decimal column appears with all zero values (but in the query editor it had decimal values)?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
84 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |