Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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

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!

 

DateStudent NameSubmissions
27/02/2020Lizui4
28/02/2020Laufenburg7
02/03/2020Tegalpapak8
05/03/2020Ar Rabiyah5
06/03/2020Gangarampur3
05/02/2021Bellegarde3
01/03/2021Luntas2
04/03/2021Frei Paulo6
04/03/2021Seedorf2
06/03/2021Bellegarde3
09/03/2021Gangarampur3
11/03/2021Cosamaloapan de Carpio7
13/03/2021Luntas2
15/03/2021Zagrodno9
05/02/2022Bellegarde3
27/02/2022Lizui4
28/02/2022Laufenburg7
01/03/2022Luntas2
02/03/2022Tegalpapak8
04/03/2022Seedorf2
04/03/2022Frei Paulo6
05/03/2022Ar Rabiyah5
06/03/2022Gangarampur3
06/03/2022Bellegarde3
09/03/2022Gangarampur3
11/03/2022Cosamaloapan de Carpio7
13/03/2022Luntas2
15/03/2022Zagrodno9


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

 

Week End DateCurrent WeekPrevious Week
01/03/2020110
08/03/20201611
07/02/2021316
07/03/2021133
14/03/20211213
21/03/2021912
06/02/202239
27/02/202243
06/03/2022364
13/03/20221236
20/03/2022912
1 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"

 

mahoneypat_0-1648295593129.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

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

 

mahoneypat_0-1648255722444.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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"

 

MakeItReal_0-1648263848406.png

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"

 

mahoneypat_0-1648295593129.png

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.