Group by week and sum current and previous week using Power Query

[Anonymous]

Hello,

I have the following table in Power Query and i want to group the data in the table by week (first day of week is Monday). In the final table, each row should have a unique-valued "End Date of Week" column and correspond to the sum of Submissions for that week. There should also be a new column showing the sum of Submissions for the previous week. Everything should be done in Power Query. Any help is much appreciated!

Date	Student Name	Submissions
27/02/2020	Lizui	4
28/02/2020	Laufenburg	7
02/03/2020	Tegalpapak	8
05/03/2020	Ar Rabiyah	5
06/03/2020	Gangarampur	3
05/02/2021	Bellegarde	3
01/03/2021	Luntas	2
04/03/2021	Frei Paulo	6
04/03/2021	Seedorf	2
06/03/2021	Bellegarde	3
09/03/2021	Gangarampur	3
11/03/2021	Cosamaloapan de Carpio	7
13/03/2021	Luntas	2
15/03/2021	Zagrodno	9
05/02/2022	Bellegarde	3
27/02/2022	Lizui	4
28/02/2022	Laufenburg	7
01/03/2022	Luntas	2
02/03/2022	Tegalpapak	8
04/03/2022	Seedorf	2
04/03/2022	Frei Paulo	6
05/03/2022	Ar Rabiyah	5
06/03/2022	Gangarampur	3
06/03/2022	Bellegarde	3
09/03/2022	Gangarampur	3
11/03/2022	Cosamaloapan de Carpio	7
13/03/2022	Luntas	2
15/03/2022	Zagrodno	9

Below is the final table that i am trying to achieve in Power Query:

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

[mahoneypat]

This probably should be done with DAX, but here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. This assumes your query is called EOW (name used for self merge in that step, so replace there is you use a different name).

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 text}, {"Student Name", type text}, {"Submissions", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-GB"),
    #"Inserted End of Week" = Table.AddColumn(#"Changed Type with Locale", "EOW", each Date.EndOfWeek([Date], Day.Monday), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Week", {"EOW"}, {{"Current Week", each List.Sum([Submissions]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "PrevWeekDate", each Date.AddDays([EOW], - 7), type date),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"PrevWeekDate"}, #"Added Custom", {"EOW"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Current Week"}, {"PreviousWeek"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Added Custom",null,0,Replacer.ReplaceValue,{"PreviousWeek"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"PrevWeekDate"})
in
    #"Removed Columns"

Pat

[Anonymous]

Hi @mahoneypat Thank you for your reply! But your resulting table has errors in the PreviousWeek column with missing values as from EOW 03/01/2020. So, it's different than the final results table in my first post.

To make it more comprehensive, first, i sorted the Date column in ascending order. Here is my attempt but i don't know how to calculate and add the column for PreviousWeek.

Maybe the trick is to add a WeekOffset column or use a Week Number index? I've been doing some research but i don't quite understand how to implement 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}})
in
    #"Grouped Rows"

[mahoneypat · Accepted solution]

Since you don't have a row for every week, I see what you mean. Is this what you are looking for?

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 with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"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}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Week End Date", Order.Ascending}}),
//#"Sorted Rows" = Table.Buffer(Table.Sort(#"Grouped Rows",{{"Week End Date", Order.Ascending}})),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Current Week"}, {"Previous Week"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index", "Index.1"})
in
    #"Removed Columns"

Note: you many need to wrap the Table.Sort step with Table.Buffer if you see incorrect results with your actual data (but I would only do that if necessary). Use the commented out line and replace the current one with it.

Pat