Group Based on Column Name

[Mic1979]

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 variables:

Header = Table.ColumnNames (#"Removed Columns2"), 

This is a list of the complete column names

SelectDollarColumns = List.Select(Table.ColumnNames(#"Removed Columns2"), each Text.Contains(_, "$")),

This is the list of the column with the $ character in

HeadersToRemove = List.Combine ({{"PHASE IN TOTAL MATURE VOLUMES"},SelectDollarColumns}),
HeaderDifference = List.Difference (Header,HeadersToRemove)

I started structing the Table.Combine function:

Grouped_Table = Table.Combine (

     #"Removed Columns2",

    HeaderDifference,

    { SelectDollarColumns, each List.Sum ()  }

I don't know how to continue.

Any suggestions?

Thanks.

[v-sgandrathi]

Hi @Mic1979,

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[Mic1979 · Accepted solution]

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}
)
)

[v-sgandrathi]

Hi @Mic1979,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.

[AntrikshSharma]

@Mic1979 If I understand correctly you want to group and then sum all the columns that have "$" in the name, correct? If yes, then try this:

let
     Source = Excel.CurrentWorkbook(){[ Name = "AR_Summary_SINGLE_UNIT_DOLLAR_Fiscal_Year_EMEA" ]}[Content],
     ChangedType = Table.TransformColumnTypes (
          Source,
          {
               { "Step", type text },
               { "Region", type text },
               { "PHASE IN TOTAL NET SALES [$]", type number },
               { "PHASE IN TOTAL MATURE VOLUMES", type number },
               { "PHASE IN TOTAL SINGLE UNIT MATERIAL DIRECT COST [$]", type number },
               { "PHASE IN TOTAL SINGLE UNIT MATERIAL OH [$]", type number },
               { "PHASE IN TOTAL SINGLE UNIT LABOR DIRECT COST [$]", type number },
               { "PHASE IN TOTAL SINGLE UNIT LABOR OH [$]", type number },
               { "PHASE IN TOTAL SINGLE UNIT DIRECT COST [$]", type number },
               { "PHASE IN TOTAL SINGLE UNIT OH [$]", type number },
               { "PHASE IN TOTAL SINGLE UNIT COGS [$]", type number }
          }
     ),
     DollarColumns = List.Select ( 
          Table.ColumnNames ( ChangedType ), 
          ( x ) => Text.Contains ( x, "$" ) 
     ),

     // Returns a List of List containing 3 items, { Column Name, Aggregate Function to Apply, Data type } 
     // this is the format in which Table.Group works
     FxAggregate = List.Transform ( 
          DollarColumns, ( x ) => 
          { 
               x, 
               // Takes the input from the current group/window of Table.Group, selects a column and applies SUM
               ( y ) => List.Sum ( Table.Column ( y, x ) ), 
               type number 
          } 
     ),

     Group = Table.Group ( 
          ChangedType, 
          { "Step", "Region" }, 
          // Pass the current group to the (y) => in FxAggregate
          // 3rd argument is basically a List of Lists { {} }
          FxAggregate 
     )
in
     Group

[Mic1979]

Hello

here the link to the samples file:

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

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.

[MarkLaf]

The group becomes much easier if you unpivot the $ columns first:

let
    Source = SampleTable,
    #"Unpivot$Cols" = Table.Unpivot(
        Source, 
        List.FindText(Table.ColumnNames(Source), "$"), 
        "Attribute", "Value"
    ),
    Group = Table.Group(
        #"Unpivot$Cols",
        {"Step", "Region"},
        {
            {"TOTAL VOLUMES", each List.Sum([PHASE IN TOTAL MATURE VOLUMES]), type nullable number},
            {"TOTAL SALES", each List.Sum([Value]), Currency.Type}
        }
    )
in
    Group

[SundarRaj]

Hi @Mic1979 , I have made some changes as per what I understood from your query. Do let me know if I have solved your query. Thanks!
https://docs.google.com/spreadsheets/d/1u3tdus_7axiZPNVX8WZWS8R6X_karx_G/edit?usp=sharing&ouid=10475...

[Mic1979]

Thanks for your suggestion, but this is not what I was looking for.

I wanted to have:

#"Grouped Rows" = Table.Group(

Source,

HeaderDifference,

{something here to sum all the columns with $ in the column name}

Hoping this clarifies.

Thanks.

[SundarRaj]

Right @Mic1979 , that clearly underlines the query. I might have misunderstood it earlier. Have a look at this one. Thanks!
https://docs.google.com/spreadsheets/d/1qEjIgfc7pLXfUU8vfT_oChhXlag0Z2-d/edit?usp=sharing&ouid=10475...

[SundarRaj]

Hi @Mic1979 , Can you send the data that you are working on along with along with the end product data? Thanks