need multiple group by in power query

[parry2k]

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

[MarcelBeug]

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

This is the one for Date and Account:

[parry2k]

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

[MarcelBeug]

Just leave out Account then:

Going off line now.

[parry2k]

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.

[parry2k]

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

[MarcelBeug · Accepted solution]

Ah, now I understand.

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

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"