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) AN...
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

     

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