In this tutorial, we show you how to round time and datetime values to the nearest 1, 15, 30 and 60 minutes in Power Query and Power BI.
Rounding time in Power Query
To round times or datetimes in Power Query, one simple formula is used.
This can either be in a custom column, or you can replace your original time or datetime data using the Table.TransformColumns function.
Here is some example datetime data we will use to show you how to do this:
To round time to the nearest 15 minutes in Power Query, create a custom column and use the following code:
=DateTime.From(
Number.Round(
Number.From([Original DateTime])
* 96)
/ 96)This essentially converts your original time or datetime values into a number, multiplies by 96, rounds it and then divides by 96 to return the correct value.
Here is what the code looks like in the custom column:
- To round time to the nearest 30 minutes in Power Query, substitute both ’96’ values in the above formula with ’48’.
- To round time to the nearest 60 minutes in Power Query, substitute both ’96’ values in the above formula with ’24’.
- To round time to the nearest minute in Power Query, substitute both ’96’ values in the above formula with ‘1440’.
Here are the results of rounding the original datetime values shown above to the nearest 1, 15, 30 and 60 minutes using this formula:
| Original DateTime | Rounded to Nearest 15 Mins PQ | Rounded to Nearest 30 Mins PQ | Rounded to Nearest 60 Mins PQ | Rounded to Nearest 1 Minute PQ |
| 01/01/2023 17:13:05 | 01/01/2023 17:15:00 | 01/01/2023 17:00:00 | 01/01/2023 17:00:00 | 01/01/2023 17:13:00 |
| 03/01/2023 06:08:56 | 03/01/2023 06:15:00 | 03/01/2023 06:00:00 | 03/01/2023 06:00:00 | 03/01/2023 06:09:00 |
| 05/01/2023 21:36:23 | 05/01/2023 21:30:00 | 05/01/2023 21:30:00 | 05/01/2023 22:00:00 | 05/01/2023 21:36:00 |
| 06/01/2023 09:42:01 | 06/01/2023 09:45:00 | 06/01/2023 09:30:00 | 06/01/2023 10:00:00 | 06/01/2023 09:42:00 |
| 07/01/2023 16:07:29 | 07/01/2023 16:00:00 | 07/01/2023 16:00:00 | 07/01/2023 16:00:00 | 07/01/2023 16:07:00 |
| 07/01/2023 16:07:30 | 07/01/2023 16:00:00 | 07/01/2023 16:00:00 | 07/01/2023 16:00:00 | 07/01/2023 16:08:00 |
| 07/01/2023 16:07:31 | 07/01/2023 16:15:00 | 07/01/2023 16:00:00 | 07/01/2023 16:00:00 | 07/01/2023 16:08:00 |
If you don’t want to add an additional column to your table with the new rounded times, you can round them in one step and replace the original values.
To do this, use Table.TransformColumns and replace the second part of the formula with the formula shown above.
Here is the formula for our example:
= Table.TransformColumns(
#"Changed Type",{{"Original DateTime", each DateTime.From( Number.Round(Number.From(_)* 96 , 0) / 96), type datetime}})You will have to amend the parts in bold with your own previous step and column name respectively.
If you want to either round up or down to the nearest 1, 15, 30 or 60 minutes in Power Query, you can change the Number.Round function to Number.RoundUp or Number.RoundDown.
Rounding time in Power BI
If you don’t want to round your time or datetime data in Power Query but instead want to round it in Power BI, you can use DAX code to do this.
To round times to the nearest 15 minutes in Power BI, you can use the MROUND DAX function.
Here is the formula we will used in our example:
M Round = MROUND('Table'[Original DateTime],"0:15")You simply need to put the table and column name with the values you want to round in the first part of the formula (in this case, Table and Original DateTime), and the interval you want in the second part of the formula (in this case 0:15 since we are rounding to the nearest 15 minutes).
You can change the interval to 0:30 or 0:60 if you want to round to the nearest 30 or 60 minutes in Power BI.
You can also use two other DAX functions (FLOOR and CEILING) to round times up or down to the nearest 15, 30 and 60 minutes.
Here they are using our example:
Floor = FLOOR('Table'[Original DateTime],"0:15")Ceiling = CEILING('Table'[Original DateTime],TIME(0,15,0))Here are the results from using these three formulas to round times in Power BI:
