Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Group Based on Column Name

Hello all,   I habe a table where I need to Table.Group function, and I would like to make it based on some character present in the column name.   Here what I did so far. I defined the following...
Need Help
  • Mic1979's avatar
    Mic1979
    1 year ago

    Hello

     

    thanks for your suggestions.

     

    Finally, I found this solution:

     

    Header = Table.ColumnNames (#"Removed Columns2"),
    SelectDollarColumns = List.Select(Table.ColumnNames(#"Removed Columns2"), each Text.Contains(_, "$")),
    HeadersToRemove = List.Combine ({{"PHASE IN TOTAL MATURE VOLUMES"},SelectDollarColumns}),
    HeaderDifference = List.Difference (Header,HeadersToRemove),

    Grouped_Table = Table.Group (
    #"Removed Columns2",
    HeaderDifference,
    List.Transform(
    SelectDollarColumns,
    (l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}
    )
    )

Mic1979's avatar
Mic1979
Post Partisan
1 year ago

Hello

here the link to the samples file:

https://docs.google.com/file/d/1022Bh9xV41EwWCmJEw5w1Oow0mQ9AzSG/edit?usp=docslist_api&filetype=msexcel

 

and this is the function I would like to change:

#"Grouped Rows" = Table.Group(Source, {"Step", "Region"}, {{"TOTAL SALES", each List.Sum([#"PHASE IN TOTAL NET SALES [$]"]), type number}, {"TOTAL VOLUMES", each List.Sum([PHASE IN TOTAL MATURE VOLUMES]), type number}})

 

I was able to replace

{"Step", "Region"} in HeaderDifference

 

What I don't know is hot to replace 

{{"TOTAL SALES", each List.Sum([#"PHASE IN TOTAL NET SALES [$]"]), type number}, {"TOTAL VOLUMES", each List.Sum([PHASE IN TOTAL MATURE VOLUMES]), type number}}

using the fact that I have the $ in the column header.

 

Probably this could appear not necessary, but this is only a sample file. The real file I have has a lot of columns, and I am looking for a way to be more rapid.

 

Thanks.