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.
I have a duration column in the power query (query editor of PBI) like below:
But when I add this to the model, it is converted to decimal, like below:
How can I extract duration from this column by dax calculated column?
Does anyone know?
Solved! Go to Solution.
Hi @Anonymous
I'm afraid you'll have to convert it to text since, as far as I know, Power BI doesn't yet handle fractions of a second in time format.
The decimal representation you have is in days. Taking that into account, you can convert it to the format you show (text) in a calculated column:
NewColumn = TRUNC(Table4[Zeit2 - Copy]) & "." & //Days FORMAT(TRUNC(Table4[Zeit2 - Copy]*24);"00") & ":" &//Hours FORMAT(TRUNC(Table4[Zeit2 - Copy]*24*60);"00") & ":" & //Minutes FORMAT(Table4[Zeit2 - Copy]*24*60*60;"00.0000000") //Seconds
You can make changes if you want to show it differently (fewer decimal places, etc. ) I've assumed the first digit before the dot is the day, correct?
In any case, I would recommend operating with the decimal value for whatever you need to do and only convert it to text at the end, when you have to display it.
you can use this:
@Anonymous
Thank you for your reply. This method only returns minutes and seconds, and it will cut out the milliseconds, which in this context are necessary
Hi @Anonymous,
@AlB is right. There isn't a duration type in the data view while the Power Query has one. I would suggest you vote up this idea.
Best Regards,
Hi @Anonymous
I'm afraid you'll have to convert it to text since, as far as I know, Power BI doesn't yet handle fractions of a second in time format.
The decimal representation you have is in days. Taking that into account, you can convert it to the format you show (text) in a calculated column:
NewColumn = TRUNC(Table4[Zeit2 - Copy]) & "." & //Days FORMAT(TRUNC(Table4[Zeit2 - Copy]*24);"00") & ":" &//Hours FORMAT(TRUNC(Table4[Zeit2 - Copy]*24*60);"00") & ":" & //Minutes FORMAT(Table4[Zeit2 - Copy]*24*60*60;"00.0000000") //Seconds
You can make changes if you want to show it differently (fewer decimal places, etc. ) I've assumed the first digit before the dot is the day, correct?
In any case, I would recommend operating with the decimal value for whatever you need to do and only convert it to text at the end, when you have to display it.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |