Forum Discussion
Solved
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...
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.)
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
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
whereColumn = "Age",
whereValue = 30,
whereColumn2 = "Age",
whereValue2 = 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(
{{whereColumn, whereValue},{whereColumn2,whereValue2}},
#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
Thanks! It's very close to the final solution but my question would be is there any way to avoid hardcoding. In lines 10 to 14 we define a variables (objects we want to iterate over), but we need to repeat hardcoding in line 28 where we need to insert {whereColumn,whereValue},{whereColumnt+1,whereValue t+1 etc}. Is there any way to automate this part of list.Accumulate?
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 resultTableThat is what I wanted. Thank's a lot! 🙂