March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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")
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")
Would this have the same issue as @ryan_mayu or suppose if my minutes is 2748 it would result in 45(hh):48(nn)?
you can try this
Column = time(int('Table'[Min]/60),mod('Table'[Min],60),0)
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
85 | |
79 | |
64 | |
59 |
User | Count |
---|---|
140 | |
122 | |
105 | |
94 | |
90 |