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 by changes by the last two parameters
*/
MonthsList = List.Transform(
{0..11},
each Date.ToText(#date(1900, 1, 1) + Duration.From(_ * 31), "MMMM", "en-Us")
),
Source = Excel.CurrentWorkbook(){[Name = "Datenbasis"]}[Content],
//Get all column names
ColNames = List.Buffer(Table.ColumnNames(Source)),
//Get all Month columns
Headers = List.Buffer(List.Intersect({ColNames, MonthsList})),
//Create a row header 'Total'
FirstCol = Table.FromColumns({{"Total"}}, {ColNames{0}}),
/*
Use List.Accumulate to take the first column 'Cost centre' and
add all the month columns with the sum over the month column to it
*/
AccTbl = List.Accumulate(
Headers,
FirstCol,
(st, cur) =>
Table.AddColumn(st, cur, each List.Sum(Table.Column(Source, cur)), type number)
),
//Combine the source table and the sum table (with one row)
CombineTbls = Table.Combine({Source, AccTbl})
in
CombineTbls
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.