Show previous week value on same row

[Anonymous]

Hello,

I have the following table in Power Query and i want to show the previous week value in a new column for each row. Is it possible? Any help is much appreciated!

Week End Date	Current Week
01/03/2020	11
08/03/2020	16
07/02/2021	3
07/03/2021	13
14/03/2021	12
21/03/2021	9
06/02/2022	3
27/02/2022	4
06/03/2022	36
13/03/2022	12
20/03/2022	9

Here is my current code which resulted in the table above:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI/b8IwEMW/SpQZifjyBxgLEiwMFWUqYjgUk1o1dmTqgX56DlLCIWzUyXn52ee797zZpDAaZjCEDLJ0kC7Vr1e0Ful2QGjMEfq9NDvvGhKjKyeY5Te+lg3qFlv8JjHueMn4m0tWuFMn/CJRdrxifIGmQYeH1jtSeV/g2oCgX1OpNd3hannn4q/AhS+9+cEjfUDHCsbmTqrkHb22JKpn/iFlbd3+frhiMHTxhPFQ54J3NrNHPKC2ZI5JapnM0LXK9jaKPD6FKBn7xMbZ2lxOTh79gXCbfbYQzxYi2d4GgIC1wFgo94LxJ2s5DOVSMv7q0UDs0fANr7KLFRB89P9mF7BJ8Ekes9ueAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Student Name" = _t, Submissions = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Student Name", type text}, {"Submissions", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week End Date", each Date.EndOfWeek([Date],Day.Monday)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Week End Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date", "Week End Date", "Student Name", "Submissions"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Week End Date"}, {{"Current Week", each List.Sum([Submissions]), type nullable number}})
in
    #"Grouped Rows"

And below is the final table that i am trying to obtain:

Week End Date	Current Week	Previous Week
01/03/2020	11	0
08/03/2020	16	11
07/02/2021	3	16
07/03/2021	13	3
14/03/2021	12	13
21/03/2021	9	12
06/02/2022	3	9
27/02/2022	4	3
06/03/2022	36	4
13/03/2022	12	36
20/03/2022	9	12

[ronrsnfld]

To add an offset column, at the end of your code:

//add offset column
    offset = Table.FromColumns(
            Table.ToColumns(#"Grouped Rows") & 
                { {0} & List.RemoveLastN(#"Grouped Rows"[Current Week])},
            type table[Date=date, Current Week=Int64.Type, Previous Week=Int64.Type]
    )
in
    offset

I have read, but not confirmed, that this method is faster than creating an Index column and using that for accessing each previous item.

Full Code

let

//your code
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI/b8IwEMW/SpQZifjyBxgLEiwMFWUqYjgUk1o1dmTqgX56DlLCIWzUyXn52ee797zZpDAaZjCEDLJ0kC7Vr1e0Ful2QGjMEfq9NDvvGhKjKyeY5Te+lg3qFlv8JjHueMn4m0tWuFMn/CJRdrxifIGmQYeH1jtSeV/g2oCgX1OpNd3hannn4q/AhS+9+cEjfUDHCsbmTqrkHb22JKpn/iFlbd3+frhiMHTxhPFQ54J3NrNHPKC2ZI5JapnM0LXK9jaKPD6FKBn7xMbZ2lxOTh79gXCbfbYQzxYi2d4GgIC1wFgo94LxJ2s5DOVSMv7q0UDs0fANr7KLFRB89P9mF7BJ8Ekes9ueAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Student Name" = _t, Submissions = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Student Name", type text}, {"Submissions", Int64.Type}},"en-150"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week End Date", each Date.EndOfWeek([Date],Day.Monday)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Week End Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date", "Week End Date", "Student Name", "Submissions"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Week End Date"}, {{"Current Week", each List.Sum([Submissions]), type nullable number}}),

//add offset column
    offset = Table.FromColumns(
            Table.ToColumns(#"Grouped Rows") & 
                { {0} & List.RemoveLastN(#"Grouped Rows"[Current Week])},
            type table[Date=date, Current Week=Int64.Type, Previous Week=Int64.Type]
    )
in
    offset

Output

Notes: 

1. Because of regional settings, I added the culture argument to your Changed Type step
2. The output dates are MDY due to my regional settings

[Vijay_A_Verma · Accepted solution]

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI/b8IwEMW/SpQZifjyBxgLEiwMFWUqYjgUk1o1dmTqgX56DlLCIWzUyXn52ee797zZpDAaZjCEDLJ0kC7Vr1e0Ful2QGjMEfq9NDvvGhKjKyeY5Te+lg3qFlv8JjHueMn4m0tWuFMn/CJRdrxifIGmQYeH1jtSeV/g2oCgX1OpNd3hannn4q/AhS+9+cEjfUDHCsbmTqrkHb22JKpn/iFlbd3+frhiMHTxhPFQ54J3NrNHPKC2ZI5JapnM0LXK9jaKPD6FKBn7xMbZ2lxOTh79gXCbfbYQzxYi2d4GgIC1wFgo94LxJ2s5DOVSMv7q0UDs0fANr7KLFRB89P9mF7BJ8Ekes9ueAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Student Name" = _t, Submissions = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Student Name", type text}, {"Submissions", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Week End Date", each Date.EndOfWeek([Date],Day.Monday)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Week End Date", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date", "Week End Date", "Student Name", "Submissions"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Week End Date"}, {{"Current Week", each List.Sum([Submissions]), type nullable number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Previous Week", each if [Index]=0 then 0 else #"Added Index"[Current Week]{[Index]-1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"})
in
    #"Removed Columns"