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?
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"Im getting the error "2 arguments were sent to a function that expects 1" from this ?
something was wrong with placement of {}
