Forum Discussion
FilipF
2 years agoFrequent Visitor
List.Accumulate() two parameters at the same time
Hello all, For the past couple of days I was discovering List.Accumulate() function. It works well but the problem appears when I need to pass two parameters at the same time. In my real case scenar...
- 2 years ago
filteredTable = Table.SelectRows(currentTable, each _[columnName] = columnValue
should be
filteredTable = Table.SelectRows(currentTable, each Record.Field(_,columnName) = columnValue
I think your sample column was probably intended to be Age not Name. What are you intending to do? Filter a table by multiple parameters? (e.g. Age = 30 and Gender = F and etc.)
Or are you trying to add the results of multiple filters separately? (Age = 30 or Gender = F or etc.)
- 2 years ago
Two different ways below
let // Sample table with columns: ID, Name, Age sourceTable = Table.FromRecords({ [ID = 1, Name = "John", Age = 25], [ID = 2, Name = "Jane", Age = 30], [ID = 3, Name = "Bob", Age = 22], [ID = 4, Name = "Alice", Age = 35] }), // Sample WHERE clause parameters whereColumnList = {"Age","Age"}, //method 1 Create two lists and use List.Zip whereValueList = {30,25}, wherePairs = //method 2 just make a list of lists { {"Age", 30}, {"Age", 25} }, // Define the accumulation function with two parameters (table and WHERE clause) accumulationFunction = (currentTable as table, columnName as text, columnValue as number) => let filteredTable = Table.SelectRows(currentTable, each Record.Field(_,columnName) = columnValue) in filteredTable, // Initial state with the source table initialState = Table.Buffer(sourceTable), // might want to try this with and without table.buffer and see which is faster // Use List.Accumulate with the wrapper function resultTable = List.Accumulate( List.Zip({whereColumnList,whereValueList}), // wherePairs will work too, don't need List.Zip in that case #table({},{}), (state, current) => Table.Combine({state,accumulationFunction(initialState, current{0},current{1})}) ) //FinalTable = Table.Combine(resultTable) Don't need this, do it in the List.Accumulate. //List.Accumulate iterates through a list in the first argument //but your seed in argument two can be any type. Make it an empty table and table combine as you iterate through the list. in resultTable
AlienSx
2 years agoSuper User
Hello, FilipF in "similar example": you can't use _[columnName] where columnName is text variable. It must be "hardcoded" otherwise use Record.Field(_, columnName)
FilipF
2 years agoFrequent Visitor
Yes, it works however please find my second message above as it's more closely what I wanted to solve (two parameters instead of once that is hardcoded)