Skip to content

Instantly share code, notes, and snippets.

Last active June 6, 2020 07:53
  • 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?
Creating a list of monthnames... format and language of the list can
easily be changed by the last two parameters
MonthsList = List.Transform({0..11}, each Date.ToText(#date(1900,1,1) +
Duration.From(_*31), "MMMM", "en-GB")),
Source = Excel.CurrentWorkbook(){[Name="Datenbasis"]}[Content],
//Get all column names
ColNames = List.Buffer(Table.ColumnNames(Source)),
//Get all Month columns
ColsToSum = List.Buffer(List.Intersect({ColNames, MonthsList})),
Determining the headers for the sum-table...
- column1 is the column with the cost centres, where we want to see
the word 'TOTAL' in the last row
- all the other columns are month columns with the sum value in the
last row
- All other columns that may occur should not be summed...
this is why they are not included in this step
Headers = Table.Buffer(Table.FromColumns({{ColNames{0}} & ColsToSum})),
Creating a table with the column names of the source table in column1
and the values of the source table in lists in column 2
Custom1 = Table.FromColumns({ColNames, Table.ToColumns(Source)}),
The merge results in the column name for the first column 'Cost centre'
and all month columns. However, it returns a NULL value for all other
columns that may occur
#"Merged Queries" =
Custom1, {"Column1"}, Headers, {"Column1"}, "Custom1", JoinKind.LeftOuter
// Expanding the columns leads to column names or NULL value
#"Expanded {0}" =
#"Merged Queries", "Custom1", {"Column1"}, {"Column1.1"}
Depending on the result of the join, generate the following calculated column
#"Added Custom" =
#"Expanded {0}",
"ColumnsValue", each
// if the join returns 'Cost centre'
if [Column1.1] = ColNames{0} then
//then create a combined list of Costs centres and 'Total'
[Column2] & {"Total"}
//if the join doesn't return 'Cost centre', but is not NULL then
if [Column1.1] <> null then
Create a combination the list of values and
the sum of values in the bottom line
[L = List.Sum([Column2]), N = [Column2] & {L}][N]
if the value is NULL, then return only
the values, but create no sum
Create a final table from the values column and
the column 1, which includes the column headers
TblFromCols =
#"Added Custom"[ColumnsValue], #"Added Custom"[Column1]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment