in Google Sheet, I use this formula to unpivot a table:

=arrayformula(SPLIT(FLATTEN(IMPORTRANGE("https://docs.google.com/spreadsheets/d/";"Foglio1!A2:A999")&"|"&IMPORTRANGE("https://docs.google.com/spreadsheets/d/";"Foglio1!AQ1:BB1")&"|"&IMPORTRANGE("https://docs.google.com/spreadsheets/d/";"Foglio1!AQ2:BB999"));"|"))

The data structure is like in the screenshot: the problem is that the unpivoting create row also when (for example) Alpha in Project A is null.

How can i fix this problem, creating unpivoted rows only if the value in the cell is > 0?

Many thanks mate!

Screenshot

CodePudding user response：

Wrap the formula in a query(), like this:

=query( arrayformula(...); "where Col3 is not null"; 1 )