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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
parry2k
Super User
Super User

need multiple group by in power query

Hi there,

 

I have dataset with following columns:

 

Account

Date

Amount

 

I want to group by data in power query

 

Group by Date, Account, Sum of Amount

Group by Date, Sum of Amount

 

Need this in Power Query

 

Thanks,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

1 ACCEPTED SOLUTION

Ah, now I understand.

 

First group by at the lowest level, next group by the higher level, adding aggregation "all rows":

 

Group by with all rows.png

 

This is the generated code, in which Value.Type(#"Grouped Rows") in step #"Grouped Rows1" is a manual replacement of the generated type table. I consider it a bug (or a mistake by Microsoft) that the nested tables with "All Rows" get type table instead of the type of the original table. As explained in this video (it's a longer video, the link jumps to the relevant fragment).

 

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"account", "date"}, {{"sum of amount", each List.Sum([amount]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"date"}, {{"total amount", each List.Sum([sum of amount]), type number}, {"AllRows", each _, Value.Type(#"Grouped Rows")}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows1", "AllRows", {"account", "sum of amount"}, {"account", "sum of amount"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllRows",{{"account", Order.Ascending}, {"date", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"account", "date", "sum of amount", "total amount"})
in
    #"Reordered Columns"

 

 

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
MarcelBeug
Community Champion
Community Champion

Looks to me like 2 straightforward group by's, so what's the issue?

 

This is the one for Date and Account:

 

Group By Account and Date.png

Specializing in Power Query Formula Language (M)

@MarcelBeugi want the another one with date only, this one is date + account



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Just leave out Account then:

 

Group By Date.png
Going off line now.

Specializing in Power Query Formula Language (M)

i need both that is what I'm looing for:

 

i need group by date + account and also by date only

 

sorry if i was not clear.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

so the expect result for following sample data:

 

account   date              amount

1              Jan 01             100

1              Jan 02               200

1              Jan 02              300

2              Jan 01             300

2              Jan 02             400

2              jan 03              500

2              jan 03              500

 

account  date      sum of amount   total amount

1             jan 01              100                400

1             jan 02               500               900

2             jan 01               300               400

2            jan 02                400               900

2            jan 03               1000               1000

        

 

sum of amount = group by account + date

total amount = group by date

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Ah, now I understand.

 

First group by at the lowest level, next group by the higher level, adding aggregation "all rows":

 

Group by with all rows.png

 

This is the generated code, in which Value.Type(#"Grouped Rows") in step #"Grouped Rows1" is a manual replacement of the generated type table. I consider it a bug (or a mistake by Microsoft) that the nested tables with "All Rows" get type table instead of the type of the original table. As explained in this video (it's a longer video, the link jumps to the relevant fragment).

 

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"account", "date"}, {{"sum of amount", each List.Sum([amount]), type number}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"date"}, {{"total amount", each List.Sum([sum of amount]), type number}, {"AllRows", each _, Value.Type(#"Grouped Rows")}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows1", "AllRows", {"account", "sum of amount"}, {"account", "sum of amount"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AllRows",{{"account", Order.Ascending}, {"date", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"account", "date", "sum of amount", "total amount"})
in
    #"Reordered Columns"

 

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors