Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
let
/*
This is Bill's version of getting a list of months...
as this is not a critical part for the over all solution,
I just copied it into this solution as well
*/
List_AllMonths = List.Transform({0..11}, each Date.ToText(#date(1900, 1, 1) +
Duration.From(_ * 31), "MMMM", "en-Us")),
Source = Excel.CurrentWorkbook(){[Name="Datenbasis"]}[Content],
//Getting all column names of the source table
List_AllColumnNames = Table.ColumnNames(Source),
//Getting all month columns from the source table
List_MonthCol = List.Intersect({List_AllColumnNames, List_AllMonths}),
/*
This list is used, to make grouping over a dynamic number of columns possible,
which is used in the next step 'grouping'
*/
List_ColsToAggregate =
List.Transform(
List_MonthCol,
each {_, (x)=> List.Sum(Record.Field(x, _)), type number}
),
/*
This step creates the sum over the columns and respects
that the number of columns can change
*/
Grouping =
Table.AddColumn(
Table.Group(
Source, {}, List_ColsToAggregate, GroupKind.Local
),
"Cost centre", each "Total"
),
//Combine the source table and the sum table
TabCombine = Table.Combine({Source, Grouping})
in
TabCombine
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.