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:
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:
One more formula:
=HSTACK(
FILTER(Table1[#All],Table1[[#All],['#]]<>"null"),
TOCOL(TEXTSPLIT(", "&TEXTJOIN(", ",FALSE,IF(Table1['#]="null",Table1[Item Name],"")),", , "))
)