Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
let
/*
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" =
Table.NestedJoin(
Custom1, {"Column1"}, Headers, {"Column1"}, "Custom1", JoinKind.LeftOuter
),
// Expanding the columns leads to column names or NULL value
#"Expanded {0}" =
Table.ExpandTableColumn(
#"Merged Queries", "Custom1", {"Column1"}, {"Column1.1"}
),
/*
Depending on the result of the join, generate the following calculated column
*/
#"Added Custom" =
Table.AddColumn(
#"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"}
else
//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]
else
/*
if the value is NULL, then return only
the values, but create no sum
*/
[Column2]
),
/*
Create a final table from the values column and
the column 1, which includes the column headers
*/
TblFromCols =
Table.FromColumns(
#"Added Custom"[ColumnsValue], #"Added Custom"[Column1]
)
in
TblFromCols
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.