Forum Discussion

eeew00's avatar
eeew00
Helper I
6 years ago
Solved

Duplicate column with offset one row inside grouped table

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

     

  • eeew00's avatar
    eeew00
    Helper I
    6 years ago

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