List.Accumulate to Add Multiple Multiple Columns in One Step

List of Functions inside List.Accumulate to add Multiple Columns in a Table:

There are various techniques to add multiple columns simultaneously in Power Query. In this post, we will explore how the List.Accumulate function can be utilized for this purpose.

1st argument,

Lst -> This is the input list structured as a nested list, containing both the function name and the column name within each list.

2nd argument,

Source -> This refers to the input table that consists of a single date column.

3rd argument,

The number of iterations corresponds to the number of items in the list. During each iteration, the Table.AddColumn function is employed to add a column to the source table.

The first item in each list serves as the column name,

While the second item in each list is used as the function to modify the date.

let

 Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],

 Lst = {{"Week",Date.WeekOfYear}, {"Month",Date.MonthName},{"Qtr",Date.QuarterOfYear}},

 Result = List.Accumulate(Lst, Source, (s,c) => Table.AddColumn(s,c{0}, each c{1}([Date])))

in

 Result

Using List.Accumulate to Add Multiple Columns Dynamically in One Step:

The dataset we have consists of monthly sales for each company presented in a crosstab table format.

Our goal is to introduce columns representing % Contribution. The number of columns to be added will depend on the number of months in our dataset. The List.Accumulate function will assist us in achieving this in a single step.

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = List.Accumulate(List.Skip(Table.ColumnNames(Source)), Source, (s,c) => Table.AddColumn(s,c & " %", (f) => Record.Field(f,c)/List.Sum(List.Skip(Record.ToList(f))), Percentage.Type))

in

 Result