Power Query: Table.Group with a dynamic list of columns specifying column type

Question

I have ttOKLostTypes=Table.Group(#"Pivoted Column", {"Index"}, List.Transform(columnList2, each {_, (grp) => List.Max(Table.Column(grp, _)) })), However this resets column types. How can I specify column types in the above transformation as here:

 #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Index"}, {{"InvoiceDate", each List.Max([InvoiceDate]),type nullable date},....

I know I can find out column types by using

schema=Table.Schema(#"Pivoted Column"),

but I cannot figure out how can I build a proper List with column types to be used in the Table.Group()

Answer 1

Thanks @Ron Rosenfeld. Your answer which works suggested me to find another way using Expression.Evaluate. Evaluate without #shared does not work. See https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/

columnList = Table.ColumnNames(#"Pivoted Column"),
columnList2 = List.RemoveItems(columnList,{"Index"}),
ColListWithTypes = List.Transform(columnList2,(colName)=> {colName,Table.SelectRows(schema,each [Name]=colName)[TypeName]{0}}),    
ttTestWithTypes=Table.Group(#"Pivoted Column", {"Index"}, List.Transform(ColListWithTypes, each {_{0}, (grp) => List.Max(Table.Column(grp, _{0})),Expression.Evaluate(_{1},#shared)})),

Answer 2

You can build a dynamic list of all the aggregations to include the data type, using List.Transform, by just adding the data type to your transformation.

Assuming the data types are all the same:

For example, if your grouping column is "Column1", then

   maxCols = List.RemoveItems(Table.ColumnNames(#"Changed Type"),{"Column1"}),
    colAggregations = 
        List.Transform(
            maxCols,
                (c)=> {c, each List.Max(Table.Column(_,c)),Int64.Type}
            ),

    group = Table.Group(#"Changed Type","Column1", colAggregations)

EDIT

To include the types of the original columns, dynamically, is more difficult. Table.Schema will return the column types as text so they have to be transformed into a Type.

One way to do this is with a custom function.

Custom Function
name it: fnTextToType
I only included a few types. The Field name is a name returned by Table.Schema for a particular type, and the field value is the type. It is hopefully obvious how to extend this function to account for other types

(txt as text) =>
let 
   typeRecord = 
    Record.Field(
        [Number.Type = Number.Type,
         Int64.Type = Int64.Type,
         DateTime.Type = DateTime.Type],
        txt
    ) 
in 
    typeRecord

Then you can use it in code like this:

    #"Changed Type" = Table.TransformColumnTypes(rem,{{"Column1", Int64.Type}, {"Column2", type number}, {"Column3", Int64.Type}}),

//get list of column types in column order
//note these are returned as text strings and not as "types"
    colTypes =  Table.Schema(#"Changed Type")[TypeName],

//create list of columns upon which to execute the aggregation (List.Max in this case)
    maxCols = List.RemoveItems(Table.ColumnNames(#"Changed Type"),{"Column1"}),

//create list of aggregations
    colAggregations = 
        List.Transform(maxCols,(c)=> {c, each List.Max(Table.Column(_,c)),
        fnTextToType(colTypes{List.PositionOf(Table.ColumnNames(#"Changed Type"),c)})}),
        
//now group them
    group = Table.Group(#"Changed Type","Column1", colAggregations)
in
    group

You can see how the types were maintained in the screenshots below.

Changed Type

group