Duplicate column with offset one row inside grouped table

[eeew00]

Hello!

Easier to explain in pictures, so:

I have grouped the data by user and what I would like to do now is to duplicate the "DateAndTime" column in each user table("Custom" column) AND also raise it by one row.. (End result is to subtract DateAndTime from the duplicated raised row to see time difference) 

I'm trying to follow https://www.youtube.com/watch?v=90EYX7pzVlE but since my data is grouped I'm not sure about how to proceed.. 

Any help would be greatly appreciated

 

[eeew00]

Thank you both @Anonymous  @Mariusz , your solutions worked well 🙂 Have a good day and thanks!

[Anonymous · Accepted solution]

try this way:

let    
...
...

    breakTab=(tab as table) =>
        let
         timeDiff=List.Accumulate(List.Numbers(1,Table.RowCount(tab)-1), {},(s,c)=>s&{tab[Time]{c}-tab[Time]{c-1}}) 
        in   Table.FromColumns({timeDiff},{"timeDiff"}),

    diff= Table.Group(yourTab, {"Group"}, {{"Diff", each breakTab(_), type table}}),
    te = Table.ExpandTableColumn(diff, "Diff", {"timeDiff"}, {"timeDiff"})
in
    te

[Mariusz]

Hi @eeew00 

You can try this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1jcw0zcyMDJQMLC0MjBTitXBkDA0sDKESBih6bCA6UCTMDQE64gFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, DateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", Int64.Type}, {"DateTime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"User"}, {{"rows", each Table.AddIndexColumn( _, "Index" ), type table [User=number, DateTime=datetime, Index=number]}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows", "rows", {"DateTime", "Index"}, {"DateTime", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded rows", "Index+1", each [Index]+1, type number ),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Index+1", "User"}, #"Added Custom", {"Index", "User"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"DateTime"}, {"Added Custom.DateTime"})
in
    #"Expanded Added Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn