Skip to content

Instantly share code, notes, and snippets.

Last active June 6, 2020 08:01
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
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 =
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 =
Source, {}, List_ColsToAggregate, GroupKind.Local
"Cost centre", each "Total"
//Combine the source table and the sum table
TabCombine = Table.Combine({Source, Grouping})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment