Forum Discussion
Super Userneed multiple group by in power query
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"
Super UserMarcelBeugi want the another one with date only, this one is date + account
Community ChampionJust 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
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"