Problems with GroupKind.Local

[aTChris]

I'm working with a dataset that has events recorded which I need to group when an attribute changes.

I used https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/

The issue I have is the End Datetime and the next rows Start DateTime has a gap.

Ideally, I need the End DateTime of the previous row to be 1 second less than the Start DateTime.

let
    Source = Excel.Workbook(File.Contents("C:/Data.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"moved", Int64.Type}, {"event_at", type datetime}, {"temp", type number}, {"co2", Int64.Type}, {"hum", type number}, {"mac", type text}, {"sn", type text}, {"message_type", Int64.Type}, {"building_id", Int64.Type}, {"timezone", type text}, {"floor_id", Int64.Type}, {"group_types", type text}, {"spaces", type text}, {"groups", type text}, {"space_types", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([message_type] = 2)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"sn"}, {{"Count", each _, type table [Column1=nullable number, moved=nullable number, event_at=nullable datetime, temp=nullable number, co2=nullable number, hum=nullable number, mac=nullable text, sn=nullable text, message_type=nullable number, building_id=nullable number, timezone=nullable text, floor_id=nullable number, group_types=nullable text, spaces=nullable text, groups=nullable text, space_types=nullable text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"moved", "event_at", "message_type", "building_id", "floor_id", "group_types", "spaces", "groups", "space_types"}, {"Count.moved", "Count.event_at", "Count.message_type", "Count.building_id", "Count.floor_id", "Count.group_types", "Count.spaces", "Count.groups", "Count.space_types"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Count", {"sn", "Count.moved"}, {{"Start", each List.Min([Count.event_at]), type nullable datetime}, {"End", each List.Max([Count.event_at]), type nullable datetime}, {"Building.ID", each List.Min([Count.building_id]), type nullable number}, {"Spaces", each List.Min([Count.spaces]), type nullable text}, {"Groups", each List.Min([Count.groups]), type nullable text}, {"SpaceType", each List.Min([Count.space_types]), type nullable text}}, GroupKind.Local)
in
    #"Grouped Rows1"

Any ideas? 

[v-lionel-msft · Accepted solution]

Hello @aTChris ,

You can use DAX to accomplish this.

1. Add an [Index] column.

2. Create a calculated column.

__start = 
VAR x = 
CALCULATE(
    MAX(Sheet6[end]),
    FILTER(
        Sheet6,
        Sheet6[Index] = EARLIER(Sheet6[Index]) - 1
    )
)
RETURN
IF(
    x = BLANK(),
    [start],
    x + 1/86400
    
)

Best regards
Lionel Chen

If this post helps,then consider Accepting it as the solution to help other members find it faster.