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
spinfuzer
2 years agoSolution Sage
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
FilipF
2 years agoFrequent Visitor
That is what I wanted. Thank's a lot! 🙂