Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- Re: Grouping times

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Grouping times

12-04-2020
05:40 PM

Hi,

I have a Table with data about Traffic Reports

ViolationCode, IssuerKey, DateOfViolation,TimeOfViolation etc..

I need to create a vizualization that shows the amount of violations for every two hours.

Therfore,

I need to group a set of times throughout a day into different categrories ( Every two hours)

i.e. 8am-10pm is group 8 to 10 am. 10am to 12pm is group 10 to 12 am and so on.

The grouping/bin fucntion is still not working for me.

can anyone please provide some help for a formula?

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2020
01:20 AM

Hi @SHAKEDALROY

Assuming you have a Table1[DateTime] column in your fact table, create another column that specifies the corresponding two-hour category.

```
Two-hour category =
VAR time_ =
Table1[DateTime] - INT ( Table1[DateTime] )
RETURN
SWITCH (
TRUE (),
time_ < 2 / 24, "00:00-02:00",
time_ < 4 / 24, "02:00-04:00",
time_ < 6 / 24, "04:00-06:00",
// Fill in all the remaining options here....
//...
time_ < 24 / 24, "22:00-00:00"
)
```

or alternatively:

```
Two-hour category V2 =
VAR time_ = Table2[DateTime] - INT ( Table2[DateTime] )
VAR aux_ = FLOOR ( time_ * 24, 2 )
RETURN
FORMAT ( aux_, "00" ) & "-" & FORMAT ( aux_ + 2, "00" )
```

You can then use this column in a chart for instance and easily build a measure that calculates the number of items per two-hour category

**Please mark the question solved when done and consider giving a thumbs up if posts are helpful.**

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2020
05:43 PM

Hi, @SHAKEDALROY

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Test:

You may create a custom column with the following m codes.

```
let
d = [Date],
h = Time.Hour(d)
in
if h>=0 and h<2 then "0-2"
else if h<4 then "2-4"
else if h<6 then "4-6"
else if h<8 then "6-8"
else if h<10 then "8-10"
else if h<12 then "10-12"
else if h<14 then "12-14"
else if h<16 then "14-16"
else if h<18 then "16-18"
else if h<20 then "18-20"
else if h<22 then "20-22"
else if h<24 then "22-24"
else null
```

Result:

Best Regards

Allan

If this post **helps**, then please consider ** Accept it as the solution** to help the other members find it more quickly.

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-07-2020
05:43 PM

Hi, @SHAKEDALROY

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Test:

You may create a custom column with the following m codes.

```
let
d = [Date],
h = Time.Hour(d)
in
if h>=0 and h<2 then "0-2"
else if h<4 then "2-4"
else if h<6 then "4-6"
else if h<8 then "6-8"
else if h<10 then "8-10"
else if h<12 then "10-12"
else if h<14 then "12-14"
else if h<16 then "14-16"
else if h<18 then "16-18"
else if h<20 then "18-20"
else if h<22 then "20-22"
else if h<24 then "22-24"
else null
```

Result:

Best Regards

Allan

If this post **helps**, then please consider ** Accept it as the solution** to help the other members find it more quickly.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2020
08:44 AM

Hi, @SHAKEDALROY , you might want to use these simple codes to create a bin table named Bins,

```
let
Source = Table.AddColumn(Table.TransformColumns(Table.FromRows(List.Split({0..23},2), {"start","end"}), {{"end", each _+1}}), "bin", each Text.From([start]) & "h to " & Text.From([end]) & "h")
in
Source
```

Then a DAX formula to count records in each period,

```
Count =
COUNTROWS (
FILTER (
Report,
HOUR ( Report[TimeOfViolation] ) >= MIN ( Bins[start] )
&& HOUR ( Report[TimeOfViolation] ) < MIN ( Bins[end] )
)
)
```

I might want to refer to * the attached file* for details.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-05-2020
01:20 AM

Hi @SHAKEDALROY

Assuming you have a Table1[DateTime] column in your fact table, create another column that specifies the corresponding two-hour category.

```
Two-hour category =
VAR time_ =
Table1[DateTime] - INT ( Table1[DateTime] )
RETURN
SWITCH (
TRUE (),
time_ < 2 / 24, "00:00-02:00",
time_ < 4 / 24, "02:00-04:00",
time_ < 6 / 24, "04:00-06:00",
// Fill in all the remaining options here....
//...
time_ < 24 / 24, "22:00-00:00"
)
```

or alternatively:

```
Two-hour category V2 =
VAR time_ = Table2[DateTime] - INT ( Table2[DateTime] )
VAR aux_ = FLOOR ( time_ * 24, 2 )
RETURN
FORMAT ( aux_, "00" ) & "-" & FORMAT ( aux_ + 2, "00" )
```

You can then use this column in a chart for instance and easily build a measure that calculates the number of items per two-hour category

**Please mark the question solved when done and consider giving a thumbs up if posts are helpful.**

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Top Solution Authors

User | Count |
---|---|

52 | |

29 | |

25 | |

20 | |

10 |

Top Kudoed Authors

User | Count |
---|---|

65 | |

48 | |

41 | |

36 | |

17 |