Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Curre...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"
ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

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