Working With Date / Time Formats

[shastie]

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.

[FrankAT · Accepted 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)

[shastie]

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?

[shastie]

Thanks all, this worked well.

[amitchandak]

@shastie , In power Bi you will not get duration same as excel

refer  the options

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...