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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Trebor84
Helper II
Helper II

Power Query number rounding issue

Hi, I have a list of accepted times and cancelled times in a table and I am trying to calculate the difference in minutes rounding up to the next minute.

 

ACCEPTEDCancelledMinutes Until Cancelled
01/01/2019 02:0601/01/2019 07:06300
01/01/2019 02:1001/01/2019 07:09300
01/01/2019 02:1001/01/2019 07:46336

 

In Excel the Minutes Until Cancelled column works as expected using the below formula:

 

=ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00")

 

I am trying to replacate this in Power Query but can't get the rounding correct using the formula:

 

=Number.RoundUp(Duration.TotalMinutes([Cancelled] - [ACCEPTED]),1) 

 

Capture.JPG

 

If I apply an additional step on the field using the built in Power Query Round Up function, I get this which is better but not sure why my original step isn't producing the same result?  Also this is still slightly wrong as the second value needs to be 300 minutes not 299.  Taking into account the seconds the value probably is 299 however I want to move this up to the next minute which the Excel formula achieves.

Capture.JPG

 

Any suggestions please?

 

2 ACCEPTED SOLUTIONS

1. =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00") - "1:00" is a character, hence it will be treated as 0 in Excel. Hence =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,0) will give the same result.

2. Now, below are results for [@Cancelled]-[@ACCEPTED] in Excel and when this is multiplied by 1440.

Below screenshot is from Excel

1.png

Now, you see the second result is 299.00000000023200. Excel's precision is 15 digits. Now, if you count 15 digits, the number would be 299.000000000232. Since Roundup is detecting 232 after decimal places, hence Roundup is making 299.00000000023200 to 300.
3. This behaviour is same in DAX also. Hence, DAX and Excel behaves the same.

4. See the below for results of Current-Accepted (This screenshot is from Power BI Desktop)

1.png

Now let me use Windows supplied calculator for second row values

DAX - 0.207638888889051*1440 : 299.0000000002334

PQ - 0.207638888888889*1440 : 299.0000000000002 

Now, in case of PQ when I count 15 digits, it comes out to be 299.000000000000. This is quivalent to 299, hence no effect due to Roundup. 

Hope, it clears up the issue for you. 

I will also suggest you to read Floating point Airtmetic issue of Excel - https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu... 

View solution in original post

collinsg
Super User
Super User

The second argument of Number.RoundUp is “digits” – the number of digits to round to. Replacing the 1 in your formula with a 0 will fix the problem.

 

Number.RoundUp( 
Duration.TotalMinutes( [Cancelled]-[Accepted] ),
0

 

View solution in original post

7 REPLIES 7
collinsg
Super User
Super User

The second argument of Number.RoundUp is “digits” – the number of digits to round to. Replacing the 1 in your formula with a 0 will fix the problem.

 

Number.RoundUp( 
Duration.TotalMinutes( [Cancelled]-[Accepted] ),
0

 

Trebor84
Helper II
Helper II

Thanks, yes that makes sense as to why Power Query displayed a different result.

 

I used a dax calculation in the end to achieve the desired result.

Fowmy
Super User
Super User

@Trebor84 

I am not sure If @MattAllington  meant the same as I tried this way

Number.RoundUp(Duration.TotalSeconds([Cancelled]-[ACCEPTED])/60)

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi, that works apart from the rounding up.

 

Capture.JPG

 

In Excel the second example gives me 300 minutes which is what I am looking for using: =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00")

 

Capture.JPG

 

Capture.JPG

 

 

1. =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00") - "1:00" is a character, hence it will be treated as 0 in Excel. Hence =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,0) will give the same result.

2. Now, below are results for [@Cancelled]-[@ACCEPTED] in Excel and when this is multiplied by 1440.

Below screenshot is from Excel

1.png

Now, you see the second result is 299.00000000023200. Excel's precision is 15 digits. Now, if you count 15 digits, the number would be 299.000000000232. Since Roundup is detecting 232 after decimal places, hence Roundup is making 299.00000000023200 to 300.
3. This behaviour is same in DAX also. Hence, DAX and Excel behaves the same.

4. See the below for results of Current-Accepted (This screenshot is from Power BI Desktop)

1.png

Now let me use Windows supplied calculator for second row values

DAX - 0.207638888889051*1440 : 299.0000000002334

PQ - 0.207638888888889*1440 : 299.0000000000002 

Now, in case of PQ when I count 15 digits, it comes out to be 299.000000000000. This is quivalent to 299, hence no effect due to Roundup. 

Hope, it clears up the issue for you. 

I will also suggest you to read Floating point Airtmetic issue of Excel - https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu... 

I think you will find that TotalMinutes ignores the seconds. Try doing the calc in seconds, then converting to mins and rounding then. Just a suggestion. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Still having trouble with this, so I have tried seconds.

 

=Duration.TotalSeconds([Cancelled]-[ACCEPTED])

 

I have then converted to what I think should be minutes.

 

=Duration.TotalMinutes([Seconds])

 

Expression.Error: We cannot convert the value 17953 to type Duration.
Details:
Value=17953
Type=[Type]

 

Capture.JPG

 

 

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors