Dynamically Expand Table Records

[rpiboy_1]

I would like to be able to dynamically expand a set of table records in columns. The number of columns can vary so I can't 'hardcode' the expansion, I need to be able to dynamically iterate over the columns. I'm struggling with how to get from Query 1 to Query 1 Result.

Let's say I have a query that looks like this:

Query 1:

Column1	Column2	Column3	Column4	[ColumnX, [...]]
[Table]	[Table]	[Table]	[Table]	[Table]

The Table records are the result of a Function, so I programmatically 'know' the contents of each table.

I want to loop through and dynamically expand each table (as I don't know how many columns there might be), I have another Query that provides a reference for how many Columns there are. Effectively, FOR EACH Index expand the Columns as listed in the adjacent columns.

Reference Query:

Index	First Embedded Column Name	Second Embedded Column Name
1	<SomeName1>	<AnotherName1>
2	<SomeName2>	<AnotherName2>
3	<SomeName3>	<AnotherName3>
4	<SomeName4>	<AnotherName4>
[x, [...]]	<SomeNameX>	<AnotherNameX>

The result would be.

Query 1 Result:

<SomeName1>	<AnotherName1>	<SomeName2>	<AnotherName2>	<SomeName3>	<AnotherName3>	<SomeName4>	<AnotherName4>	[<SomeNameX>, [...]]	[<AnotherNameX>, [...]]
<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>
<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>
<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>
<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>	<value>

[rpiboy_1]

I may have logic'ed this out, but would appreciate feedback.

I beleive that if I transpose Query 1 so that it looks like this:

Query 1

Index	Table
1	[Table]
2	[Table]
3	[Table]
4	[Table]

I can use List.Accumulate to iterate over this table. [Index] will be my list to iterate. My 'seed' value will be a starting table with some other columns I need. Then my function will expand the 'Current' value (table from Query1[Table]) then merge it with my 'Start' (State) table. It will loop through each row, iteralively expanding and merging the columns.

Conceptually does this make sense?

[v-jingzhang]

Hi @rpiboy_1 

Transposing is a good idea. Another option you can try is Unpivoting columns. This will give you a result similar to below, which has column names in "Attribute" column and has table records in "Value" column. Then you can expand the "Value" column easily. 

After expanding, you can use Pivot Columns feature to reverse the table structure back.  

Unpivot columns - Power Query | Microsoft Learn

Pivot columns - Power Query | Microsoft Learn

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[rpiboy_1 · Accepted solution]

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"