Forum Discussion
Group Based on Column Name
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}
)
)
Hello
here the link to the samples file:
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.
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