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 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.

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

10 Replies

  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

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

  • 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.

     

      • Mic1979's avatar
        Mic1979
        Post Partisan
        1 year ago

        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.

         

    • MarkLaf's avatar
      MarkLaf
      Super User
      1 year ago

      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

       

       

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    1 year ago

    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

     

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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's avatar
      Mic1979
      Post Partisan
      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}
      )
      )

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        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.