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)
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?