PQ Function to Group by All Non-Number Columns

[cr731]

I would like to write a Power Query function which lets me pass a table as a parameter, and the function will output a table which groups the input table by all non-numeric columns. I'd like for it to be dynamic so I don't need to specify any column labels... just pass a table and it will group by each non-numeric column and then sum each of the numeric columns. Is that possible?

 

[Comfy]

I think so.

(InTbl as table) as table =>


let  
    NumCols = Table.ToList(Table.SelectColumns(Table.SelectRows(Table.Schema(InTbl), each ([Kind] = "number")),{"Name"})),
    OtherCols = Table.ToList(Table.SelectColumns(Table.SelectRows(Table.Schema(InTbl), each ([Kind] <> "number")),{"Name"})),


    #"Unpivoted Columns" = Table.UnpivotOtherColumns(InTbl, OtherCols, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", List.Combine({{"Attribute"}, OtherCols}), {{"Amount", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Amount", List.Sum)
in
    #"Pivoted Column"

The datatypes should be set prior to passing the table to the function.

 

[Comfy]

I forgot to paste some updates in time.

(InTbl as table) as table =>


let  
    NumCols = Table.ToList(Table.SelectColumns(Table.SelectRows(Table.Schema(InTbl), each ([Kind] = "number")),{"Name"})),
    OtherCols = Table.ToList(Table.SelectColumns(Table.SelectRows(Table.Schema(InTbl), each ([Kind] <> "number")),{"Name"})),
    
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(InTbl, OtherCols, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", List.Combine({{"Attribute"}, OtherCols}), {{"Amount", each List.Sum([Value]), type number}}),
    #"Pivoted Column" = if List.Count(NumCols) = 0 or List.Count(OtherCols) = 0 then Table.FromList({[Error Message="Columns Types were not set correctly"]}, Record.FieldValues, {"Error Message"}) else Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Amount", List.Sum)
in
    #"Pivoted Column"