Merge multiple single-column tables into one multi-column

[JasonWS]

I cannot for the life of me figure this out. I'll be pasting data from each column of a table in an email into their own respective text boxes. I want to split all text boxes, and combine them together into a single collection. I have the split for each box working beautifully, however my attempt to collect them together results in nested collections. Is there a way to expand each nested table to become the whole column?

Or instead, is there a way to combine two one-column tables so each table is a column? All text boxes should have the same number of items. If they don't, I can figure that out ahead of time.

Here's my code that results in the nested collection. The Filter() and Len() is to omit the last empty item that exists when pasting from an excel or emailed table.

ClearCollect(
    colCustMat,
    {
        'Customer Material': Filter(
            Split(
                txtCustMatList,
                Char(10)
            ),
            Len(Result) > 0
        ),
        'Internal Material': Filter(
            Split(
                txtIntMatList,
                Char(10)
            ),
            Len(Result) > 0
        )
    }
)

I have this working beautifully in Excel and Visual Basic for Applications, but I'd like to transition that to PowerApps.

[v-qiaqi-msft · Accepted solution]

Hi @JasonWS,

Do you want to expand all the nested columns within your collection?

Modify your formula as below:

With(
     {
      'Customer Material':Filter(Split(txtCustMatList,Char(10)),Len(Result) > 0),
      'Internal Material': Filter(Split(txtIntMatList,Char(10)),Len(Result) > 0)
     },
     ForAll(
         Sequence(CountRows('Customer Material')),
         Collect(colCustMat,{
                             'Customer Material':Last(FirstN('Customer Material',Value)).Result, 
                             'Internal Material':Last(FirstN('Internal Material',Value)).Result
                            }
         )
     )
)

[JasonWS]

I appreciate the reply. This looks like it would work beautifully! Thank you!