Forum Discussion
Duration not summing up correctly
amitchandak The problem seems to be that prior to the summing function, it appears that time over 24 hours is not 'seen' after the import occurs.
pre import excel format (HH:MM:SS):
shows in excel formula bar as DATE/TIME :
When it imports to PBI - The column is FORMATTED to TIME and displays as HH:MM:SS but the time shown is WRONG. As you can see below, it is showing the actual TIME stamp as reflected in the above screenshot vs the duration of 161:48 that it should
Does this help?
Hi Jtischler ,
It is beacause that in Power Query Editor, there are two data types not currently present in Data or Report View: Date/Time/Timezone and Duration.
When a column with these data types is loaded into the model and viewed in Data or Report view, a column with a Date/Time/Timezone data type will be converted into a Date/Time, and a column with a Duration data type is converted into a Decimal Number.
You can try to use FORMAT function or use DURATION in Power Query.
Column = FORMAT('duration'[duration],"hh:mm:ss")
Please reference the following document.
FORMAT function (DAX) - DAX | Microsoft Learn
Solved: Re: Excel time format [h]:mm:ss on power BI - Microsoft Power BI Community
Data types in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.