Forum Discussion

fmasselink's avatar
fmasselink
Regular Visitor
4 years ago
Solved

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) ...
Need Help
  • User101's avatar
    User101
    4 years ago

    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)

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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"