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, likely a basic question here.
I have a table in excel that I would like to link to Powerbi. The figures in the table are a result of subtracting one date and time away from another to give me a duration. To then put that in to hh:mm in excel that is formatted in excel to a custom format [h]:mm:ss.
When I import this into BI in then shows it as a date / time the same as excel does when you click in the cell. How can i get PowerBI to show the formatted figures?
Thanks in advance.
Solved! Go to Solution.
Hi @shastie
you can get it with a calculated column in DAX like this:
Formatted =
VAR _DaysInHours = INT('Table'[DateTime]) * 24
VAR _Hours = INT(MOD('Table'[DateTime],1) * 24)
VAR _Minutes = FORMAT(MOD('Table'[DateTime],1),"nn:ss")
RETURN
_DaysInHours + _Hours & ":" & _Minutes
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Hi @shastie
you can get it with a calculated column in DAX like this:
Formatted =
VAR _DaysInHours = INT('Table'[DateTime]) * 24
VAR _Hours = INT(MOD('Table'[DateTime],1) * 24)
VAR _Minutes = FORMAT(MOD('Table'[DateTime],1),"nn:ss")
RETURN
_DaysInHours + _Hours & ":" & _Minutes
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Actually Frank, or anyone else who may be able to help.
While this worked well it has left any blank cells displaying the : seperator between hours and minutes. How do i remove this?
Thanks all, this worked well.
@shastie , In power Bi you will not get duration same as excel
refer the options
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 |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |