Multiply all possible combinations for rows - Cross Join set row with multiple columns

Question

Hi I have a similar problem in Google sheets that is solved in this thread:

Generate all possible combinations for Columns(cross join or Cartesian product)

i am trying to join multiple rows with number of columns i tried with FLATTEN & TRANSPOSE but wasn't given me what i wanted

Below is a google sheet showing what i have (DATA) and wanted (SHOULD LOOK LIKE) DATASET

any help would be appreciated!

i tried with excel & power query which is fine but i want to do it in google sheet but can't

Answer 1

Try the following formula (see your gs harun24hr sheet).

=QUERY(
HSTACK(MAKEARRAY(ROWS(DATA!A2:D5)*COLUMNS(DATA!E1:P5),ROWS(DATA!A2:D5),LAMBDA(r,c,INDEX(DATA!A2:D5, ROUNDUP(r/COLUMNS(DATA!E1:P5),0),c))),
FLATTEN(SPLIT(REPT(TEXTJOIN("|",1,DATA!E1:P1)&"|",4),"|")),
FLATTEN(DATA!E2:P5)),
"select * where Col6 is not null",0)

Answer 2

The Cartesian product that automatically expands as new rows are added can be performed like this:

SPLIT(
  TOCOL(
    DATA!A2:A&"|"&DATA!B2:B&"|"&
    DATA!C2:C&"|"&DATA!D2:D&"|"&
    DATA!E1:P1&"|"&DATA!E2:P,3),
  "|")

However, all those ranges have empty rows at the bottom which slow down the formula so it's better to remove them:

=ARRAYFORMULA(
   LET(data,QUERY(DATA!A2:P,"where Col1 is not null"),
       months,DATA!E1:P1,
       QUERY(
         SPLIT(
           TOCOL(
             INDEX(data,,1)&"|"&INDEX(data,,2)&"|"&
             INDEX(data,,3)&"|"&INDEX(data,,4)&"|"&
             months&"|"&CHOOSECOLS(data,SEQUENCE(12,1,5)),3),"|"),
         "where Col6 is not null")))

Answer 3

The question specifies Cartesian product but the desired results provided in the sample spreadsheet indicate that what the asker wants is an unpivot.

Here's how to unpivot the data using a lambda function:

=let( 
  unpivot_, lambda(data, numFixedCols, numColsPerGroup, let(k,n(numFixedCols),d,if(k,data,hstack(sequence(rows(data)),data)),f,if(k,k,1),g,numColsPerGroup,r_,lambda(t,o,h,chooserows(t,sequence(h,1,o))),c_,lambda(t,o,w,choosecols(t,sequence(1,w,o))),s_,lambda(r,c,h,w,r_(c_(d,c,w),r,h)),h,hstack(s_(1,1,1,f),"Month","Amount"),i,sequence(1,(columns(d)-f)/g,f+1,g),a,reduce(h,sequence(rows(d)-1,1,2),lambda(a,r,let(x,s_(r,1,1,f),b,reduce(tocol(æ,2),i,lambda(y,c,let(z,s_(r,c,1,g),if(single(z)="",y,vstack(y,hstack(x,s_(1,c,1,1),z)))))),vstack(a,b)))),if(k,a,choosecols(a,sequence(1,columns(a)-1,2))))), 

  unpivot_(DATA!A1:P, 4, 1) 
)

The formula is in cell doubleunary!A1 in the sample spreadsheet.

See let() and lambda().