List.Accumulate() two parameters at the same time

[FilipF]

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

[spinfuzer · Accepted solution]

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.)

[spinfuzer · Accepted solution]

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.)

[FilipF]

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

[spinfuzer]

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

[FilipF]

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?

[spinfuzer · Accepted solution]

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]

That is what I wanted. Thank's a lot! 🙂

[AlienSx]

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]

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)