Forum Discussion
Grouping & summarizing using column name lists
Hi,
I have a table (very simplified) with table headers that have a / (and my other columns also have some with a double-colon)
(not sure how relevant that is with referencing the column/row)
| Seller | revenue/volume | revenue/amount |
| John | 3 | 10 |
| Marc | 1 | 4 |
| John | 2 | 3 |
| Marc | 2 | 8 |
I want to perform a group action in PowerQuery, while I need to reduce the size of my resulting .pbix file.
I'm aware I will loose detail by doing the below, though that is as planned.
I have the following steps in my Query Editor:
A1 = Table.ColumnNames(source) // the source table from above
A2 = { "revenue/volume", "revenue/amount" } // list with columns to summarize over
A3 = List.Difference(A1, A2) // get list with columns without columns from A2
In the next step I want to group and summarize over the columns in A2, but I'm uncertain of how to do that...
A4 = Table.Group (source, A3, ????????) // group by A3, summarize by ....
Within the section of the question marks I want to do two things:
1. Add a counter "revenue/numberofsales" that counts the rows that are grouped together.
{"revenue/numberofsales", each Table.RowCount(_), Int64.Type}This works (if this was the only thing in there... but I want to concatenate two lists...)
2. I want to Summarize over all the columns that are in A2 with a function rather than writing
{"sumField1", each List.Sum([#"sumField1"]), type nullable number},
{"sumField2", each List.Sum([#"sumField2"]), type nullable number},
...,
{"sumField9", each List.Sum([#"sumField9"]), type nullable number}like in
each columnname in A2, do
{ _, each List.Sum([_]), type nullable number }
done
return as list
Any suggestion on how to do that with function?
That should result in something like this then (I guess)
A4 = Table.Group (source, A3, List.Combine(
{
{"revenue/numberofsales", each Table.RowCount(_), Int64.Type}
},
{
{<something that returns the "each columnname in A2" as a list>}
}
)
)
providing this as the resulting table
| Seller | revenue/volume | revenue/amount | revenue/numberofsales |
| John | 5 | 13 | 2 |
| Marc | 3 | 12 | 2 |
Perhaps you are looking for something like this:
let
Source = Sql.Databases("......"),
AdventureWorksDW2019 = Source{[Name="AdventureWorksDW2019"]}[Data],
dbo_DimProduct = AdventureWorksDW2019{[Schema="dbo",Item="DimProduct"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_DimProduct,{"ProductKey", "ProductAlternateKey", "LargePhoto", "HebrewDescription", "ArabicDescription", "ChineseDescription", "TurkishDescription", "JapaneseDescription", "ThaiDescription", "GermanDescription", "FrenchDescription"}),
allColumns = Table.ColumnNames(#"Removed Columns"),
aggCols = {"ListPrice", "DealerPrice"},
remaining = List.Difference(allColumns, aggCols),
aggs = List.Transform(aggCols, (c) => {c, (t) => List.Sum(Table.Column(t, c)), type nullable number })
in
Table.Group(#"Removed Columns", "EnglishProductName", aggs)
6 Replies
Perhaps you are looking for something like this:
let
Source = Sql.Databases("......"),
AdventureWorksDW2019 = Source{[Name="AdventureWorksDW2019"]}[Data],
dbo_DimProduct = AdventureWorksDW2019{[Schema="dbo",Item="DimProduct"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_DimProduct,{"ProductKey", "ProductAlternateKey", "LargePhoto", "HebrewDescription", "ArabicDescription", "ChineseDescription", "TurkishDescription", "JapaneseDescription", "ThaiDescription", "GermanDescription", "FrenchDescription"}),
allColumns = Table.ColumnNames(#"Removed Columns"),
aggCols = {"ListPrice", "DealerPrice"},
remaining = List.Difference(allColumns, aggCols),
aggs = List.Transform(aggCols, (c) => {c, (t) => List.Sum(Table.Column(t, c)), type nullable number })
in
Table.Group(#"Removed Columns", "EnglishProductName", aggs)
That is exactly what I was looking for 🙂
So my piece of code now looks like, nice and clean to add a new column and summarize in the same action:
A4 = Table.Group (source, A3, List.Combine(
{
{"revenue/numberofsales", each Table.RowCount(_), Int64.Type}
},
List.Transform(A2, (c) => {c, (t) => List.Sum(Table.Column(t, c)), type nullable number })
)
)One additional question: how come the "(t)" and "t" works inside the List.Transform? How does "t" know it is the table that is being grouped?
We can think of .. List.Transform .. Just returns a function that accepts t and returns a column c.
Table.Group expects a function.
We can determine the parameter type using value.type
let
t1 = Table.Group(
Table.FromRecords(
{[CustomerID= 1, price = 5]}), "CustomerID", {"paramType",each Value.Type(_)}),
#"1" = t1{[CustomerID=1]}[paramType]
in
#"1"
Hi fmasselink
I guess you may have multiple columns when try to groupby, and multiple columns you want to do List.Sum, otherwise it is not worth the effort...I am not sure how to groupby, but here is another way, all with List.Sum but your result was showing one as Table.RowCount(_)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTIGYkMDpVidaCXfxKJkEA+ITcACUCVGYGVIKkACFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Seller = _t, #"revenue/volume" = _t, #"revenue/amount" = _t]), A1 = Table.ColumnNames(Source), // the source table from above A2 = { "revenue/volume", "revenue/amount" } , // list with columns to summarize over A3 = List.Difference(A1, A2), A4 = Table.Distinct( Table.SelectColumns(Source, A3)), A5 = Table.NestedJoin(A4, A3, Source, A3, "Custom", JoinKind.LeftOuter), Custom1 = List.Accumulate(A2, A5, (state, current) => Table.AddColumn(state, current, each List.Sum(List.Transform( Table.ToList( Table.SelectColumns(_[Custom],current)),Number.From)))), #"Removed Columns" = Table.RemoveColumns(Custom1,{"Custom"}) in #"Removed Columns"
