Get Values from column ON EACH Table and Append to itself

[JustDavid]

PQ Gurus,

Apologies in advanced that I can't share sample pbix file as i don't know how to create a "table" in each row.

Hopefully my screenshot can show you and that you're able to understand what I desired to achieve.

This is the result of one of the applied steps (call it 'ColumnarForEachRecord').

As you can see, in the [Columnar Data] column (denotes by the top right of screenshot), I have Table "result" on each row. And when I click on each of that Table "result", the preview below shows that I'd have 3 columns (To, From and Alloc). However, the number of rows will differ from Table to Table. In this screenshot, it shows there's 3 rows.

What I'd like to achieve here is an M language where:

1. Loop through each Table row.
2. On that table, get the UNIQUE values on 'To' column (denotes by the red box on the left side on screenshot).
3. Add those UNIQUE values to the 'To' and 'From' column at the bottom (denotes by the red boxes on the bottom right side of screenshot)
4. Assign 1 to 'Alloc' column on the newly added rows (denotes by the green box on the bottom right side of screenshot).

Apologies on doing a terrible job on my screenshot to show my desired result, instead of a duplicate 30069101, it should only appear ONCE.

Was able to resolve this on my own.

//create a temp table to get the distinct 'To' values
        innerTable_DistinctToValues = Table.SelectColumns( Table.Distinct(renameTier1Col, {"To"}), {"To"} ),
        innerTable_DuplicateTo_to_From = Table.RenameColumns(innerTable_DistinctToValues, {"To", "From"}),
        joinInnerTables = Table.Join(
                                Table.AddIndexColumn(innerTable_DistinctToValues, "Idx1", 0, 1), "Idx1",
                                Table.AddIndexColumn(innerTable_DuplicateTo_to_From, "Idx2", 0, 1), "Idx2",
                                JoinKind.FullOuter
                            ),
        innerTable_Add_AllocColumn = Table.RemoveColumns(Table.AddColumn(joinInnerTables, "Alloc", each 1), {"Idx1", "Idx2"}),
        AppendTables = Table.Combine({renameTier1Col, innerTable_Add_AllocColumn})

[v-agajavelly]

Hi @JustDavid 

Just checking in to see if the solution I shared worked out for your scenario in Power Query?
If you're seeing any unexpected behaviour or need help tweaking it further, feel free to let me know happy to assist.

And if it did the trick, it'd be great if you could mark it as accepted so it can help others facing similar challenges too.

Thanks,
Akhil.

[v-agajavelly]

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.