Google sheet - Unpivot table and skip blank cells

Question

I cannot work this out:

In Google sheet, I'm trying to unpivot a wide and long table by using this formula:

=ARRAYFORMULA((SPLIT(FLATTEN(sheet1!$J$3:$J&"//"&sheet1!$O$2:$CA$2&"//"&sheet1!$O$3:$CA);"//")))

However, this table is full of empty cells and I'd like to skip them during the process because the output of the formula in sheet2 columns A B and C is too long for nothing.

So far, I've only managed to process it again using:

=QUERY('sheet2'!A1:C;"SELECT A,B,C WHERE C >0";0)

Which works great. However I need to do it in one step because my future set of data will be longer, so I'll probably reach the far end of the sheet (and I'd prefer not having an intermediary table).

Can anyone work this out?

Answer 1

Try this on sheet2:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(sheet1!$J$3:$J&"//"&sheet1!$O$2:$CA$2&"//"&sheet1!$O$3:$CA);"//");"where Col3 is not null";0))

Answer 2

you just put the second part of what you did around the first:

=QUERY(ARRAYFORMULA((SPLIT(FLATTEN(sheet1!$J$3:$J&"//"&sheet1!$O$2:$CA$2&"//"&sheet1!$O$3:$CA);"//")));" WHERE Col3 >0";0)