This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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