none
creating a list including a function RRS feed

  • Question

  • I need to supply parameters dynamically to the Table.Group() function as the names and number of columns to group  in the table on can change.

    The relevant parameter is a list of elements each of which is a list of three items: column name, aggregation function, column type.

    So I am trying to create a list which looks like this:

    {{Column1, each List.Sum([Column1], type number}, {Column2, each List.Sum([Column2]), type number} .... {ColumnX, each List.Sum([ColumnX]), type number)}}

    he plan is then to supply that list to the Table.Group function and all would be OK.

    I can use List.Generate() to create most of the list, and the column name and column type parameters are no issue.  I am stumped however how to create the aggregation function parameter as a list item in List.Generate.  Even if I specify the list item as having type function, it steadfastly treats it as text.

    Any help appreciated.  Or if I am going about this entirely the wrong way, please tell me.

    M

    Sunday, November 18, 2018 7:54 PM

Answers

  • Yes, this is a more advanced concept (btw: any reason why you don't unpivot your columns, group on one column and then pivot-back?)

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYjMQbagUqwMRMgJic5CQEVjICcg0BmILkJAxXMgEiC1BQiZgIWcg0xTENQARpkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, T1 = _t, T2 = _t, T3 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"T1", Int64.Type}, {"T2", Int64.Type}, {"T3", Int64.Type}}),
        MyColumns = {"T1", "T2", "T3"},
        MyGroupFunction = (SourceTable) => List.Transform(MyColumns,
                                                (ColumnNames) => {ColumnNames,
                                                    (SourceTable) => List.Sum(Table.Column(SourceTable, ColumnNames)),
                                                type number}
                                                ),
        Result = Table.Group(#"Changed Type", {"Product"}, MyGroupFunction(#"Changed Type"))
    in
        Result

    Key is "MyGroupFunction", where you "simply" transform your existing lists of column names to the desired list-function. You need a function that will ingest the table where it shall pick the columns from as a parameter. Hence you end up with a nested function, as you have to create the function-part for the group-function as well.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by MaBacc Sunday, November 25, 2018 11:53 AM
    Sunday, November 25, 2018 6:32 AM
    Moderator
  • Thanks, I can now see it.  That's really appreciated.

    Not just doing a pivot/unpivot for two reasons.  (1) I thought it would be easier simply to provide a parameter list (obviously not) but (2) I don't always want to group on one column.  But I might re-look at that at some stage.

    Thanks again

    M


    • Marked as answer by MaBacc Sunday, November 25, 2018 11:53 AM
    Sunday, November 25, 2018 11:53 AM

All replies

  • Can you share the M code you're trying to use to generate the list?

    Thanks,
    Ehren

    Wednesday, November 21, 2018 6:59 PM
    Owner
  • A bit tricky since I have tried a whole load of things and none worked.  Fundamentally, what I am trying to do is:

    let

        // Objective is  to generate a list like this:
        //  List0= {{"T1", each List.Sum([T1]), type number}, {"T2", each List.Sum([T2]), type number}, {"T3", each List.Sum([T3]), type number}},
        // which can be used as the aggregatedColumns parameter of the Table.Group function

        MyColumns = {"T1","T2","T3"},
        CountofColumns = List.Count(MyColumns),
        List0 = List.Generate(()=>[index=0,item=MyColumns{0}],
                each [index]<CountofColumns,
                each [index=[index]+1],
                each [item = {MyColumns{[index]}, **"each List.Sum(["& MyColumns{[index]} & "])"**, type number}]),

    in
        List0

    Now clearly the bit I have marked **..** won't work (I wouldn't expect it to) as it will generate the right text for the function but as text and simply removing the quote marks and calling the whole thing a function won't either!

    I've tried creating a separate function: 

    let
        GenerateFunction = (L as list, i as number) as function => each List.Sum(L{[i]})
    in
        GenerateFunction

    then Invoking that in place of the **..** :

       List0 = List.Generate(()=>[index=0,item=MyColumns{0}],
                each [index]<CountofColumns,
                each [index=[index]+1],
                each [item = {MyColumns{[index]}, Function.Invoke(GenerateFunction,{MyColumns,[index]}), type number}]),

    and whilst seems to put a function into the list, it still won't work as it sees the items in the list as a list of records rather than a list of list.  Trying to convert the records into a list fails as it seems to change the function field back into text.

    Everything else I've tried failed as well, hence my question here.  How do I get a list where each item is a list comprising of a text field, a function field and a type field?

    M

    Saturday, November 24, 2018 7:09 PM
  • Yes, this is a more advanced concept (btw: any reason why you don't unpivot your columns, group on one column and then pivot-back?)

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYjMQbagUqwMRMgJic5CQEVjICcg0BmILkJAxXMgEiC1BQiZgIWcg0xTENQARpkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, T1 = _t, T2 = _t, T3 = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"T1", Int64.Type}, {"T2", Int64.Type}, {"T3", Int64.Type}}),
        MyColumns = {"T1", "T2", "T3"},
        MyGroupFunction = (SourceTable) => List.Transform(MyColumns,
                                                (ColumnNames) => {ColumnNames,
                                                    (SourceTable) => List.Sum(Table.Column(SourceTable, ColumnNames)),
                                                type number}
                                                ),
        Result = Table.Group(#"Changed Type", {"Product"}, MyGroupFunction(#"Changed Type"))
    in
        Result

    Key is "MyGroupFunction", where you "simply" transform your existing lists of column names to the desired list-function. You need a function that will ingest the table where it shall pick the columns from as a parameter. Hence you end up with a nested function, as you have to create the function-part for the group-function as well.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by MaBacc Sunday, November 25, 2018 11:53 AM
    Sunday, November 25, 2018 6:32 AM
    Moderator
  • Thanks, I can now see it.  That's really appreciated.

    Not just doing a pivot/unpivot for two reasons.  (1) I thought it would be easier simply to provide a parameter list (obviously not) but (2) I don't always want to group on one column.  But I might re-look at that at some stage.

    Thanks again

    M


    • Marked as answer by MaBacc Sunday, November 25, 2018 11:53 AM
    Sunday, November 25, 2018 11:53 AM