Get Values from column ON EACH Table and Append to itself

Hi JustDavid 

Thanks for reaching out on the Microsoft Fabric Community Forum.

I recently faced a scenario in Power Query where each row in my main table contained a nested table (in a column called "Columnar Data"). Inside each of these nested tables, I had three columns. To from and Alloc.

What we needed to do is for each nested table.

• Get the distinct values from the To column.
• Add those values back into the same table — as both To and From (essentially duplicating them).
• Assign Alloc = 1 for these new rows.
• Finally, append these rows to the original nested table, but only if that To value wasn't already present (to avoid duplicates).

Here’s how I achieved it using M code.

let

// Simulated source table with nested tables

  Source = Table.FromRows({

        {".xlsx", 30060, Table.FromRows({

            {"30069101", "30069001", 1},

            {"30069101", "30069091", 1},

            {"30069102", "30069092", 1}

        }, {"To", "From", "Alloc"})},

        {".xlsx", 30790, Table.FromRows({

            {"30069103", "30069005", 1},

            {"30069103", "30069007", 1}

        }, {"To", "From", "Alloc"})}

    }, {"Name", "Column1", "Columnar Data"}),

 // Add new self-loop rows per distinct To

 AddSelfRows = Table.AddColumn(Source, "ExpandedTable", each

  let

            origTable = [Columnar Data],

            distinctTo = Table.Distinct(Table.SelectColumns(origTable, {"To"})),

            // Generate new rows where From = To and Alloc = 1

            selfRows = Table.AddColumn(distinctTo, "From", each [To]),

            selfRowsWithAlloc = Table.AddColumn(selfRows, "Alloc", each 1),

            // Filter out if a self-row already exists (just to be safe)

            newRowsOnly = Table.SelectRows(selfRowsWithAlloc, each not List.Contains(Table.TransformColumns(origTable, {{"To", Text.From}, {"From", Text.From}})[From], Text.From([To]))),
// Combine
final = Table.Combine({origTable, newRowsOnly})
in
final
),
// Expand result if needed
 ExpandResult = Table.ExpandTableColumn(AddSelfRows, "ExpandedTable", {"To", "From", "Alloc"})
in
ExpandResult

Turns out, the self-join with index and full join isn't necessary. You can simply take distinct To, add From = To, and Alloc = 1, and then append it to the original table.

------------------------------------------------------------------------------------------------------------------------------
If this solution works for you, please consider marking it as accepted so others facing a similar issue can benefit too.

Regards,
Akhil.