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