Dynamically Expand Table Records

For posterity, I did end up using List.Accumulate, just took two days to wrap my head around the function and applying to my specific need.

Things to remember about List.Accumulate:

• It iterates purely based on the number of items in the list you feed it in the 1st argument. So there is no need for any type of 'counter' (which you often have to use in Looping in other languages).
• Since the function accepts any data in the first two arguements, the table that is a record in my list (column) that I pass comes in as a 'table'. I was bit hung up on the need to 'access' the table from the list but that isn't the case. Fundamentally when you pass what is commonly referred to as the 'State' arguement in this case as a table, that is what you've got, a Table.
• This in turn meant I could jump right to a 'merge' function to merge the State & Current tables together and expand the table on the right side of the join clause. There was a bit more complexity here as to dynamically expand the Current table, you have to get a valid list of column names, but I digress.
• From there is smooth sailing, the result of the Merge is passed back as the next State, and Current is the next table in the list.

The code is relatively stand-alone, with only two external references to other queries supplying tables of data to be merged/iterated over (comments are notes I've left for myself so that hopefully the next time it doesn't take two days, hah!):

let
    //get table to be referenced in List.Accumulate
    #"SeedTable" = SeedTable,

    //this is the function that is called later in the List.Accumulate step.
    #"ExpandMerge" = 
        (State as table, Current as table)=>
        let
            #"Merged Queries" = Table.NestedJoin(State, {"Index"}, Current, {"Index"}, "State", JoinKind.LeftOuter),
            //remove 'extra' index column to avoid overlap on next table expansion
            #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),
            //Get the headers from the state tables    
            Headers = 
                let
                    #"Headers" = Table.AddColumn(#"Removed Columns", "Headers", each Table.ColumnNames(Record.Field(_, "State"))),
                    TblHeaders = Headers{0}[Headers]
                in
                    TblHeaders,
            //expand the state table
            #"ExpandState" = Table.ExpandTableColumn(#"Removed Columns", "State", Headers)

        in
            #"ExpandState",

    //sets up table to be used to iterate in List.Accumulate. The result is a column ( [UpdatedTbl] ) with tables as Records that will be iterated over.
    #"Invoked Custom Function" = Table.AddColumn(#"ColumnPairs", "UpdatedTbl", each FnReplaceValues(#"Table to Update", [Status], [Codes])),    

    //Iteration. Remember that List.Accumulate will iterate for however many rows there are in the list (column) that is passed in the first arguement. There is no need for a counter or for tracking how many loops there are.
    
    //Also recall that any type of data can be passed. In this case we are passing a column that has tables, that table will be fully expanded in the function, there is no need to 'do' anything to the table input, it will be a table, operations going forward modify the table accordingly.
    #"IterateColumnExpansion" =
        List.Accumulate(
            #"Invoked Custom Function"[UpdatedTbl], //column with tables to modify/use
            #"SeedTable", //starting point, in this case some core columns to 'start' the table
            (State, Current)=>
                #"ExpandMerge"(State, Current)//we go through and add the columns from each table in the initial column resulting in a single table outputed that has all of the desired columns.
        )

in
    #"IterateColumnExpansion"