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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## Group and convert rows of 30 minutes intervals into the ranges of start and end times

Hi all

(This problem could be solved in either M language or DAX)

I've been searching for the solution to this problem for a while (I found the answers for converting, but only the other way around). As the last resort I decided to post it here! 🙏

I think the input and the output table (expected results) are self-explanatory.

In the following input table and output table, I've added some font colors, so it's easier to spot/compare the corresponding rows in both tables.

The input table

 Day Start of 30minTime Interval Attribute Index(from 0 to 3) Shop Sat 02:00 0 A Sat 02:30 0 A Sat 03:00 0 A Sat 03:30 0 A Sat 09:00 0 A Sat 09:30 0 A Sat 00:00 1 A Sat 00:30 1 A Sat 01:00 1 A Sun 14:00 0 A Sun 14:30 0 A Sun 15:00 0 A Sat 20:00 0 B Sat 20:30 0 B Sat 21:00 0 B Sat 20:00 2 B Sat 20:30 2 B Sat 21:00 2 B Sat 20:00 2 C Sat 20:30 2 C Sat 21:00 2 C Mon 01:00 2 C

The output table:

 Day Time Ranges Attribute Index(from 0 to 3) Shop Sat 02:00-03:59 0 A Sat 09:00-09:59 0 A Sat 00:00-01:29 1 A Sun 14:00-15:29 0 A Sat 20:00-21:29 0 B Sat 20:00-21:29 2 B Sat 20:00-21:29 2 C Mon 01:00-01:29 2 C

Notice that in the expected table the first row and the second row have the same Day, Attribute and Shop values, but because there is a time gap those are displayed separately.
Also note last row of both tables.

Many thanks for trying to solve this! 💐

Cheers
Jan

3 REPLIES 3
Super User

You're right

` Table.Group(   Table.AddIndexColumn(Source, "Index30", 0, 30, Int64.Type),    {"Day", "Start of 30min","Attribute Index", "Shop", "Index30"},    {{"Time Ranges", each       Time.ToText(List.Min([Start of 30min]), "hh:mm")       & "-"      & Time.ToText(List.Max([Start of 30min])+#duration(0,0,29,0), "hh:mm"), type text}},    GroupKind.Local,    (state, current) => Byte.From(         (current[Index30]-state[Index30])<>Duration.TotalMinutes(current[Start of 30min]-state[Start of 30min])      or current[Day]<>state[Day]      or current[Attribute Index]<>state[Attribute Index]      or current[Shop]<>state[Shop]      ))`

Stéphane

Super User

Hi,

`letSource = YourSource,Add_Index30= Table.AddIndexColumn(Source, "Index30", 0, 30, Int64.Type),Add_TimeRange = Table.Group(   Add_Index30,   {"Day", "Start of 30min","Attribute Index", "Shop", "Index30"},   {{"Time Ranges", each         Time.ToText(List.Min([Start of 30min]), "hh:mm") & "-" &        Time.ToText(List.Max([Start of 30min]) + #duration(0,0,29,0), "hh:mm"),        type text}},   GroupKind.Local,   (state, current) =>      Byte.From(       (current[Index30]-state[Index30])       <>Duration.TotalMinutes(current[Start of 30min]-state[Start of 30min])     ))in Add_TimeRange`

Stéphane

Helper I

Thanks Stéphane

I think the Add_Index30= Table.AddIndexColumn() is off. It creates indices with an increment of 30 (so 0, 30, 60, 90 etc).
I guess we need that additional column to somehow capture the continous 30-min intervals (so that we could separate those from the cases where there is a time break and then use it in grouping)?

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors