Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ParthDatwani123
Frequent Visitor

Convert Minutes to Hrs and minutes and make it sum

Hi All,

 

I am tryiing tp convert my table which is stored in minutes

sample

 Number            Mins

     3                    60

     4                    80 

 

Now I want to convert them into 1hr 0mins and 1 hr 20 mins and also want it to be able to summarize them as SUM

so in table, I can get 2 hrs 20 mins

I have tried 2 following methods, which converts the whole thing into Text and is no longer summable
Method 1

let

DecHrs = [TOTAL EXPECTED MINS]/60,

WholeHrs = Number.Round(DecHrs),

Minutes = ((DecHrs)-(WholeHrs))*60,

WholeMin = (DecHrs) - (Minutes),

FormattedTime =  Number.ToText(WholeHrs) & " hrs " & Number.ToText(Minutes) & " mins"

in

    FormattedTime)

Method 2 

let

        TotalMinutes = [TOTAL EXPECTED MINS],

        Hours = TotalMinutes / 60, WholeHrs = Number.Round(Hours),

        Minutes = (Hours- (WholeHrs))*60,

        FormattedTime = Text.PadStart(Text.From(WholeHrs), 2, "0") & ":" & Text.PadStart(Text.From(Minutes), 2, "0")

    in

        FormattedTime

Apart from that, I have thought of making 2 separate columns and using them, But feels lengthy for multiple uses

Appreciate the assistance in getting the solution and thank in advance

2 ACCEPTED SOLUTIONS

ParthDatwani123
Frequent Visitor

After summing, if anyone had negative values like I did, try this method that worked for me. Other methods made negative 1.5 to 2 on rounding.

TASK AVAILABLE HRS LEFT = 
VAR _all = SUM([MIN Table])/ 60
VAR _sign = IF(_all < 0, -1, 1)
VAR a_all = ABS(_all)
VAR _Hrs = INT(a_all)
VAR _Min = ROUND((a_all - _Hrs) * 60, 0)
RETURN 
IF(_sign < 0, "-", "") & _Hrs & ":" & FORMAT(_Min, "00")

 

View solution in original post

5 REPLIES 5
ParthDatwani123
Frequent Visitor

After summing, if anyone had negative values like I did, try this method that worked for me. Other methods made negative 1.5 to 2 on rounding.

TASK AVAILABLE HRS LEFT = 
VAR _all = SUM([MIN Table])/ 60
VAR _sign = IF(_all < 0, -1, 1)
VAR a_all = ABS(_all)
VAR _Hrs = INT(a_all)
VAR _Min = ROUND((a_all - _Hrs) * 60, 0)
RETURN 
IF(_sign < 0, "-", "") & _Hrs & ":" & FORMAT(_Min, "00")

 

Ahmedx
Super User
Super User

pls try this

Screenshot_2.pngScreenshot_3.png

Would this have the same issue as @ryan_mayu  or suppose if my minutes is 2748 it would result in 45(hh):48(nn)?

pls try this

Screenshot_1.png

ryan_mayu
Super User
Super User

@ParthDatwani123 

you can try this

Column = time(int('Table'[Min]/60),mod('Table'[Min],60),0)

11.PNG

 

however, the problem is if the hour is more than 23, it will display incorrect.

maybe you can try to add all mins, and transfer to the text type at last.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.