Forum Discussion
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 scenario, I have an sql connector and I need to pass two parameters:
let
Source = Table.FromRows(
{
{"material_table","db1"},{"customer_table","db2"}
},
{"Table","Schema"}
),
Qry = (tbl as text) =>
let
Source = Databricks.Catalogs(p_server_hostname, p_http_path, [Catalog=null, Database=null, EnableAutomaticProxyDiscovery=null]),
Database = Source{[Name=p_db,Kind="Database"]}[Data],
gold_Schema = Test_Database{[Name="silver_sap",Kind=(parameter I want to pass is schema name so in first iteration db1))]}[Data],
transactions_view = gold_salesforce_Schema{[Name= (parameter I want to pass is table name so in first iteration material_table ) ,Kind="View"]}[Data],
SelectColumn = Table.SelectColumns(transactions_view,{"lakehouse_inserted_at"}),
Distinct = List.Distinct(SelectColumn[lakehouse_inserted_at]),
Table = Table.FromRecords(
{
[Table = tbl,Refresh = Distinct{0}]
}
)
in
Table,
Iteration = List.Accumulate(Source[Source] (Here we should pass a list but how to pass a pair of parameters),{}, (s,c) => s & {Qry(c)}),
CombineData = Table.Combine(Iteration),
Because it's hard to test a code, below find very similar example.. here Power Query raise an error that column name was not found. But is there anyone who can solve this and correct?
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 = "Name",
whereValue = 30,
// 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 _[columnName] = columnValue)
in
filteredTable,
// Initial state with the source table
initialState = sourceTable,
// Use List.Accumulate with the wrapper function
resultTable = List.Accumulate(
{{whereColumn, whereValue}},
initialState,
(state, current) => accumulationFunction(state, current{0},current{1})
)
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.)
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
8 Replies
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)
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.)
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 FinalTablelet // 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
