Forum Discussion
Grouping & summarizing using column name lists
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?
Im getting the error "2 arguments were sent to a function that expects 1" from this ?