Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Creating a list of monthnames...
format and language of the list can easily by changes by the last two parameters
MonthsList = List.Transform(
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(
(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})
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.