Grouping & summarizing using column name lists

[fmasselink]

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

[User101 · Accepted solution]

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)

[fmasselink]

@User101 

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?

[MicChrDk]

Im getting the error "2 arguments were sent to a function that expects 1" from this ?

[MicChrDk]

something was wrong with placement of {}

[User101]

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"

[Vera_33]

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"