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

Reply
JanRak
Helper I
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

DayStart of 30min
Time Interval
Attribute Index
(from 0 to 3)
Shop
Sat02:000A
Sat02:300A
Sat03:000A
Sat03:300A
Sat09:000A
Sat09:300A
Sat00:001A
Sat00:301A
Sat01:001A
Sun14:000A
Sun14:300A
Sun15:000A
Sat20:000B
Sat20:300B
Sat21:000B
Sat20:002B
Sat20:302B
Sat21:002B
Sat20:002C
Sat20:302C
Sat21:002C
Mon   01:002C


The output table:

DayTime Ranges     Attribute Index
(from 0 to 3)
Shop
Sat02:00-03:590A
Sat09:00-09:590A
Sat00:00-01:291A
Sun14:00-15:290A
Sat20:00-21:290B
Sat20:00-21:292B
Sat20:00-21:292C
Mon   01:00-01:292C


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
slorin
Super User
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

 

slorin
Super User
Super User

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 

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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