Forum Discussion
List.Accumulate() two parameters at the same time
- 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
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.)
- FilipF2 years agoFrequent Visitor
Yes, indeed. Should be Age instead of Name.
Anyway, I implemented required changes and the code works as expected. Thanks a lot!
But let me ask a further question. How the code should look like if we would like to select/change two records instead of one? I've applied changes to the code but it will return error of course...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 = sourceTable, // Use List.Accumulate with the wrapper function resultTable = List.Accumulate( {{whereColumn, whereValue},{whereColumn2,whereValue2}}, initialState, (state, current) => accumulationFunction(state, current{0},current{1}) ), FinalTable = Table.Combine(resultTable) in FinalTable- spinfuzer2 years agoSolution Sage
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- FilipF2 years agoFrequent Visitor
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?