Forum Discussion
Show previous week value on same row
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 |
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"
2 Replies
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"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 offsetI 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 offsetOutput
Notes:
- Because of regional settings, I added the culture argument to your Changed Type step
- The output dates are MDY due to my regional settings
