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

[JanRak]

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 30min Time 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

[slorin]

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

[slorin]

Hi,

let
Source = 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 

[JanRak]

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)?