Power Query adding n columns iteratively calling a custom function each time

[Anonymous]

I have a custom function which undertakes some ETL and then adds a column which is a List.Sum or Text.Combine of the values in some other columns. It works fine but I have to do this 60+ times to build the final table. Again it works okay but it's pretty slow and inelegant. I was wondering if there is a way to call this function n times (i.e. 60+ times) passing 2 parameter values from a table (or perhaps 2 lists)? Alternatively is there some other approach or methodology that could make this run quicker & simpler? 

The calling function is as follows. I can put the 2nd and 3rd parameter into a table or a pair of lists, so I imagine there might be a way to iterate over the table or list and run the function for each row or item.

    let

        Set1 = fxAggregatedSet(TableName as table, "TimeSpentAwayFromOffice" as text, "TimeSpentAwayFromOffice" as text, ColumnsToUpdate as table, UniqueSetNames as table) as table,
        Set1a = fxAggregatedSet(Set1 as table, "TimeSpentInOffice" as text, "TimeSpentInOffice" as text, ColumnsToUpdate as table, UniqueSetNames as table) as table,
        Set1b = fxAggregatedSet(Set1a as table, "TimeSpentOther" as text, "TimeSpentOther" as text, ColumnsToUpdate as table, UniqueSetNames as table) as table

etc etc 60+ times

The custom function which adds the column is:

let fxAggregatedSet = (TableName, SetName, SetNameRoot, ColumnsToUpdate, UniqueSetNames) =>

let

// get list of columns to be added together from main lookup table
	SetNameLength = Text.Length(SetNameRoot),
	Set_List = Table.ToList(Table.SelectRows(Table.SelectColumns(ColumnsToUpdate, "NewColumnNames"), each Text.Start([NewColumnNames], SetNameLength) = SetNameRoot )),

// Get ZeroColumn value as text
        Step1 = Table.SelectRows(UniqueSetNames, each [RootNames] = SetName ),
        Step2 = Table.SelectColumns(Step1, "ZeroColumns"),
        Step2a = Table.AddColumn(Step2, "TrueFalseText", each if [ZeroColumns] = true then "True" else "False"),
        Step2b = Table.SelectColumns(Step2a, "TrueFalseText"),
        Step3 = Table.ToList(Step2b),

	AddAggregatedColumn = 
		if not List.Contains(Table.ToList(Table.SelectColumns(UniqueSetNames,"RootNames")), SetName) then /* if there's no column to update */
			if List.Contains(Step3, "True") then /* if it's a zero column */
				Table.AddColumn(TableName, SetName, each 0) 
			else
				Table.AddColumn(TableName, SetName, each null)
		else
			if List.Contains(Step3, "True") then /* if it's a zero column */
				Table.AddColumn(TableName, SetName, each /* use List.Sum to add percentage values e.g. 0.1, 0.8 */
					List.Sum(
						Record.FieldValues(
							Record.SelectFields(_, Set_List) 
						)
					)
					, Percentage.Type
				) 
			else
				Table.AddColumn(TableName, SetName, each /* use Trim and Combine to combine strings (actually it is lots of blanks and a string) */
				    Text.Trim(
						Text.Combine(
							Record.FieldValues(
								Record.SelectFields(_, Set_List) 
							)
						)
					)
				, type text
				)
in
    AddAggregatedColumn 
in 
    fxAggregatedSet

[Anonymous · Accepted solution]

I use List.Accumulate to Add Columns to tables frequently. For example, I could use the code at the bottom to add calculated columns where Calc2 = Column1+Column2 and Calc3 = Column2+Column3, etc.

Regards,

Mike

Table1

letter	Column1	Column2	Column3	Column4
a	1	2	3	4
b	4	5	6	7

Table2

letter	Column1	Column2	Column3	Column4	Calc2	Calc3	Calc4
a	1	2	3	4	3	5	7
b	4	5	6	7	9	11	13

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CalcColumns = {1..List.Count(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Column")))-1},
    AddCalcColumn = List.Accumulate(CalcColumns, Source, (s,c) => Table.AddColumn(s, "Calc"&Text.From(c+1), each List.Sum( Record.ToList(Record.SelectFields(_, {"Column" & Text.From(c), "Column" & Text.From(c+1)}) ))))
in
    AddCalcColumn