How to Merge Rows values into a Column on a prior Row?

Question

In power query, I'm attempting to create a formula that creates a new column called "Options" to contain the following combined row information together ignoring null values.

Example Table where rows that are filled in with yellow are the row values I want to have combined into prior row where column "#" doesn't contain a null value:

Desired Result, wish to have row values combined and delimited by a comma:

Answer 1

I created the solution for you:

It utilizes this formula:

[G2] =LET(
  c,COLUMNS(Table1),at_l,INDEX(Table1,0,c),n,ROWS(Table1),
  af,FILTER(HSTACK(Table1,SEQUENCE(n)),Table1[Product]<>"null"),
  af_l,INDEX(af,0,c+1),aw,DROP(VSTACK(af_l,n+1),1),
  HSTACK(
    TAKE(af,,c),
    MAP(af_l,aw,LAMBDA(i,j,TEXTJOIN(", ",FALSE,DROP(TAKE(at_l,j-1),i))))
  )
)

For anybody who is interesting in the algorithm:

1. Add numeration to the source table.
2. Filter the table by product only. The resulting array will be used as the part of the whole result.
3. Create the vertical vector from the numeration column shifted up one row then add at the end the total rows plus 1. These two columns define start and end rows of the source table for each product entry.
4. For each product, join Item Names from start row to the end row exclusively and attach this column to the result.