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